# Get FirstDate and LastDate of Every month from two Dates

• Comments posted to this topic are about the item Get FirstDate and LastDate of Every month from two Dates

• Why write a slow unwieldy multi-statement table-valued function when an inline table-valued function will do?

`DECLARE`

`@StartDate DATE = '05/02/2015',`

`@EndDate DATE = '11/25/2015'`

`SELECT`

`ID = n+1,`

`FirstDate = DATEADD(MONTH,n, StartPoint),`

`LastDate = DATEADD(DAY,-1,DATEADD(MONTH,n+1, StartPoint)),`

`[Month] = MONTH(DATEADD(MONTH,n, StartPoint)),`

`[Year] = YEAR(DATEADD(MONTH,n, StartPoint))`

`FROM (`

`SELECT TOP(1+DATEDIFF(MONTH, @StartDate, @EndDate))`

`n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,`

`StartPoint = CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate),0) AS DATE)`

`FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),`

`(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),`

`(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n)`

`) d`

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• the GetFLdatelist function does not take into account for the month of Feburary. If you use Feb as a start date the results look like this:

12015-02-01 00:00:00.0002015-02-28 00:00:00.00022015

22015-03-01 00:00:00.0002015-03-28 00:00:00.00032015

32015-04-01 00:00:00.0002015-04-28 00:00:00.00042015

42015-05-01 00:00:00.0002015-05-28 00:00:00.00052015

52015-06-01 00:00:00.0002015-06-28 00:00:00.00062015

62015-07-01 00:00:00.0002015-07-28 00:00:00.00072015

72015-08-01 00:00:00.0002015-08-28 00:00:00.00082015

82015-09-01 00:00:00.0002015-09-28 00:00:00.00092015

92015-10-01 00:00:00.0002015-10-28 00:00:00.000102015

102015-11-01 00:00:00.0002015-11-28 00:00:00.000112015

Notice the end dates are on the 28th vs. 30th or 31st.

• Looks like it has some problems.

• I would go with a simple recursive statement.

DECLARE @dateone AS DATE = '2012-01-01'

DECLARE @datetwo AS DATE = '2015-04-12'

;WITH cte AS (

SELECT 1 AS month_

UNION ALL

SELECT 1+ month_

FROM cte

WHERE month_ <= DATEDIFF(MONTH, @dateone, @datetwo)

)

select *

, DATEADD(DAY, 1, EOMONTH(@dateone, month_-2)) AS first_of_month

, EOMONTH(@dateone, month_-1)

from cte

• david.fundakowski (11/9/2015)

I would go with a simple recursive statement.

DECLARE @dateone AS DATE = '2012-01-01'

DECLARE @datetwo AS DATE = '2015-04-12'

;WITH cte AS (

SELECT 1 AS month_

UNION ALL

SELECT 1+ month_

FROM cte

WHERE month_ <= DATEDIFF(MONTH, @dateone, @datetwo)

)

select *

, DATEADD(DAY, 1, EOMONTH(@dateone, month_-2)) AS first_of_month

, EOMONTH(@dateone, month_-1)

from cte

I didn't know you could do that with EOMONTH(), so thanks!

rCTE's are a very expensive way to generate rows:

`DECLARE @dateone AS DATE = '1815-01-01';`

`DECLARE @datetwo AS DATE = '2015-04-12';`

`DECLARE @NOW DATETIME = GETDATE();`

`;WITH cte AS (`

`SELECT 1 AS month_`

`UNION ALL`

`SELECT 1+ month_`

`FROM cte`

`WHERE month_ <= DATEDIFF(MONTH, @dateone, @datetwo)`

`)`

`SELECT ID = month_`

`, DATEADD(DAY, 1, EOMONTH(@dateone, month_-2)) AS FirstDate`

`, EOMONTH(@dateone, month_-1) AS LastDate`

`, MONTH(EOMONTH(@dateone, month_-1)) AS [Month]`

`, YEAR(EOMONTH(@dateone, month_-1)) AS [Year]`

`FROM cte`

`OPTION (MAXRECURSION 0);`

`SELECT DATEDIFF(MILLISECOND,@NOW,GETDATE());`

`----------------------------------------------------------------------------------------------------------`

`DECLARE`

`@StartDate DATE = @dateone,`

`@EndDate DATE = @datetwo;`

`SET @NOW = GETDATE();`

`SELECT`

`ID = n,`

`FirstDate = DATEADD(DAY, 1, EOMONTH(@dateone, n-2)),`

`LastDate = EOMONTH(@dateone, n-1),`

`[Month] = MONTH(EOMONTH(@dateone, n-1)),`

`[Year] = YEAR(EOMONTH(@dateone, n-1))`

`FROM (`

`SELECT TOP(1+DATEDIFF(MONTH, @StartDate, @EndDate))`

`n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))`

`FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),`

`(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),`

`(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),`

`(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)`

`) d;`

`SELECT DATEDIFF(MILLISECOND,@NOW,GETDATE());`

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw