Get First and Last date of month list

Situatie

Mai jos este o functie ce primeste ca parametrii doua date calendaristice si pentru intervalul cuprins intre prima si a doua data, returneaza o lista ce contine data de inceput a lunii, data de sfarsit, numarul si anul aferent lunii listate.

Solutie

— SELECT ID,FirstDate,LastDate,Month,Year FROM [GetFLDatelist](‘20210702′,’20220925’)

CREATE FUNCTION [dbo].[GetFirstDateList]
(@StartDate DATE,
@EndDate DATE)
RETURNS @DateTable TABLE (
ID INT IDENTITY,
FirstDate DATETIME,
LastDate DATETIME,
[Month] INT,
[Year] INT)

BEGIN
DECLARE @EndDate1 DATE,
@StartDate1 DATE,
@EndDate2 DATE
SET @StartDate1 = ( SELECT DATEADD(month, DATEDIFF(month, 1, @StartDate), 0) )
SET @EndDate1 = ( SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @StartDate) + 1, 0)) )
SET @EndDate2 = ( SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @EndDate) + 2, 0)) );
WITH Numbers (Number) AS ( SELECT row_number()
OVER (
ORDER BY object_id)
FROM sys.all_objects )
INSERT INTO @DateTable
SELECT dateadd(month, Number – 1, @StartDate1),
dateadd(month, Number – 1, @EndDate1),
MONTH(dateadd(month, Number – 1, @StartDate1)),
YEAR(dateadd(month, Number – 1, @StartDate1))
FROM Numbers
WHERE number <= datediff(month, @StartDate, @EndDate2)
RETURN
END
GO

Tip solutie

Permanent

Follow Us