Get last day of month (SQL Server 2008R2)

Question

I have the following scenario. I need the last days of any month from 2013 till today.

How can I combine these two sql statements?

SELECT DATEADD(m, 0, GETDATE()) --today

SELECT DATEDIFF(m, '2013-01-01', GETDATE()) --60 months for 5 years

I need this result:

LastDayOfMth
=======================
2013-01-31 00:00:00.000
2013-02-28 00:00:00.000
2013-03-31 00:00:00.000
2013-04-30 00:00:00.000
2013-05-31 00:00:00.000
....
2017-12-31 00:00:00.000

score 1 · Answer 1 · answered Jan 11 '18 at 09:40

Use Recursive CTE

DECLARE @Strt DATE = '2013-01-01'
;WITH CTE
AS
(
    SELECT
        SeqNo = 1,
        MyDate = DATEADD(M,1,DATEADD(D,-1,@Strt))

    UNION ALL

    SELECT
        SeqN = SeqNo +1,
        MyDate = DATEADD(m, 1, MyDate)
        FROM CTE
            WHERE SeqNo < DATEDIFF(m, @Strt,GETDATE())

)
SELECT
    MyDate
    FROM CTE
    OPTION (maxrecursion 0)

score 1 · Answer 2 · answered Jan 11 '18 at 09:44

You can also do it by executing dynamic SQL query. Use a WHILE loop to get that last date of all the months from the start date till today's date.

Query

declare @today as date = getdate();
declare @start as date = '2013-01-01';
declare @months as int;
select @months = datediff(month, @start, @today);
declare @i as int= 1;
declare @t as table([dt] date);
while(@i <= @months)
begin
    insert into @t 
    select dateadd(day, -1, (dateadd(month, @i, @start)));
    set @i += 1;
end
select * from @t;

Find a demo here

This is *procedural thinking*, not inlineable, not useable in a `VIEW`... — Shnugo, Jan 11 '18 at 09:50

score 1 · Answer 3 · answered Jan 11 '18 at 09:46

Try this

DECLARE @StartDate DATE='2013-01-01';

WITH Tally AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr 
    FROM master..spt_values
)
SELECT DATEADD(DAY,-1,DATEADD(MONTH,Nmbr,@StartDate)) AS EndOfNextMonth
FROM Tally
WHERE DATEADD(MONTH,Nmbr,@StartDate)<GETDATE();

The Tally cte will comeback with a list of running numbers. master..spt_values is just a table with quite a lot of rows...

The query will add one month to the date and reduce it by one day.

score 1 · Answer 4 · answered Jan 11 '18 at 09:52

I use a numbers table, but for this example I have generated one using a CTE. I generate a list of numbers from -58 to + 1 which I use to add this number of months to the current date. I then take away the number of days in this date to get me to the end of the month:

WITH Numbers
AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY a.Num) * -1 + 2 AS Num
    FROM (VALUES (1),(2),(3),(5),(4),(6),(7),(8),(9),(10)) AS a(Num)
    CROSS APPLY
        (VALUES (1),(2),(3),(5),(6),(7)) As b(Num)
),
DateMinusMonths
AS
(
    SELECT DATEADD(m, Num , CAST(getdate() AS Date)) As d
    FROM Numbers
)
SELECT DATEADD(d, day(d) * -1, d) EOM
FROM DateMinusMonths

Any solution that uses a numbers table gets an upvote from me. They're so handy. — David Rushton, Jan 11 '18 at 10:23

score 1 · Accepted Answer · answered Jan 11 '18 at 10:22

1

This is the type of code I use when trying to generate dates, I've altered it to select the last day of the first month you wanted (31st January 2013) and asked it to keep adding 1 month until today's date:

SELECT
    DATEADD(MONTH, Number, '2013-01-31') AS [Date]
FROM
    master..spt_values
WHERE
    Type = 'P'
    AND DATEADD(MONTH, Number, '2013-01-31') <= GETDATE()

answered Jan 11 '18 at 10:22

DarkMark

129
7

That was a perfect solution for my approach. Many thanks :)) – yuro Jan 11 '18 at 12:40

score 0 · Answer 6 · answered Jan 11 '18 at 09:53

Following code will be helpful to you,

declare @sDate datetime='2013-01-01', @eDate datetime= GETDATE()
;WITH mycte
     AS (SELECT EOMONTH(@sDate) AS dt
         UNION ALL
         SELECT DATEADD(month, 1, dt) AS dt
         FROM   mycte
         WHERE  dt <@eDate      
         )

SELECT  EOMONTH(dt) AS LastDayOfMth
FROM   mycte
Order by LastDayOfMth

David Rushton · Answer 7 · 2018-01-11T10:26:55.990

This formula is a little complex but it avoids the need to loop or recurse.

-- End of current month.
-- Replace GETDATE() with your column.
SELECT 
    CAST(DATEADD(SECOND, -1, DATEADD(MONTH, DATEDIFF(MONTH, '2000-01-01', GETDATE()) + 1, '2000-01-01')) AS DATE) AS EOMonth
;

It calculates the number of months that have passed since the base date (I've used 2001-01-01 but you could use the first of any month). It adds those same number of months back to the base date. That gives us the start of next month. Removing a second takes us back to the end of the current month. Finally casting as a date removes the time element.

Alternatively, you could add a calendar table to your database (aka date dimension). I have one in nearly every database I manage and I use them every single day. Here's a really simple example:

Date         StartOfMonth  EndOfMonth
2018-01-01   2018-01-01    2018-01-31
2018-01-02   2018-01-01    2018-01-31
2018-01-03   2018-01-01    2018-01-31
...          ...           ...
2018-01-31   2018-01-01    2018-01-31

Starting with SQL Server 2012 you can use the EOMonth function. I know this isn't an option for the OP but it might help others.

Get last day of month (SQL Server 2008R2)

7 Answers7