Get a list of end of months dates between two months in SQL Server

Question

This is similar to this mySQL question except I only want the end of month dates: Get a list of dates between two dates

I want to be able to enter two dates, something like:

SELECT EndOfMonth('1/1/2015', '1/1/2017');

And the returned results should look like

EndOfMonth
1/31/2015
2/28/2015
3/31/2015
.
.
.
10/31/2016
11/30/2016
12/31/2016

The SQL Server version I'm using is 2008, so I actually don't have access to EOMONTH().

What version of sql server are you working with? – Zohar Peled May 25 '17 at 00:17 — Zohar Peled, May 25 '17 at 00:17

Zohar Peled · Accepted Answer · 2017-05-25T02:36:00.597

Using a numbers table makes things easier. If you don't already have a numbers table you can use the following sql to create one (taken from this SO post):

SELECT TOP 10000 IDENTITY(int,0,1) AS Number
INTO Tally
FROM sys.objects s1       
CROSS JOIN sys.objects s2 

ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (Number)

To learn more about the numbers table and how to use it, read Jeff Moden's The "Numbers" or "Tally" Table: What it is and how it replaces a loop article.

Once you have a numbers table, it's fairly easy with versions 2012 or higher, using the EOMONTH built in function:

DECLARE @Start date = '2015-01-01', @End date = '2017-01-01'

SELECT EOMONTH(DATEADD(MONTH, Number, @Start))
FROM Tally
WHERE Number < DATEDIFF(MONTH, @Start, @End)

For earlier versions, you can use DATEADD with DATEDIFF to get the last day of the previous month, and then simply add one month:

SELECT DATEADD(DAY, -DATEPART(DAY, @Start), (DATEADD(MONTH, Number+1, @Start)))
FROM Tally
WHERE Number < DATEDIFF(MONTH, @Start, @End)

See a live demo on rextester

score 3 · Answer 2 · answered May 25 '17 at 00:45

You can achieve this easily using Common Table expressions(CTE).

--Declaration of start date and end date
declare @StartDate datetime='2017-01-01', @endDate datetime='2017-12-01'

-- Expression
;WITH monthcte
     AS (SELECT EOMONTH(@StartDate) AS dates
         UNION ALL
         SELECT DATEADD(month, 1, dates) AS dates
         FROM   monthcte
         WHERE  dates <@endDate     
         )

-- Select query over expression
SELECT  EOMONTH(dates) AS EndOfMonth
FROM   monthcte
Order by EndOfMonth

score 0 · Answer 3 · answered May 25 '17 at 00:30

If you are not able to use EOMonth you can generate like this below:

declare @d1 date = '1/1/2015'
declare @d2 date = '1/1/2017'

select top (datediff(MONTH,@d1,@d2)+1) Dates = DateAdd(day,-1, Dateadd(M,Row_number() over(order by (select null)), @d1)) from
    master..spt_values n1, master..spt_values n2

If EoMonth is available in your SQL Server Version then you can try as below:

select top (datediff(MONTH,@d1,@d2)+1) Dates = EoMonth(Dateadd(M,Row_number() over(order by (select null))-1, @d1)) from
    master..spt_values n1, master..spt_values n2

score 0 · Answer 4 · answered May 25 '17 at 00:34

List of Month:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
set @StartDate = '01/01/2015'
set @EndDate = '01/01/2017'

;WITH cte1 (S) AS (
SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (S)
),
cte2 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte1 AS cte2),
cte3 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte2 AS cte2)

select distinct cast(result as date) result from
(SELECT TOP (DATEDIFF(day, @StartDate, @EndDate) + 1)
        result = DATEADD(day, ROW_NUMBER() OVER(ORDER BY S) - 1, @StartDate)
FROM cte3) as res

Get a list of end of months dates between two months in SQL Server

4 Answers4