CODEWITHSUNDEEP
sql-servert-sql
Roger Dodger
Roger Dodger

Reputation: 987

TSQL get last day of previous months upto a specified month

I need to get last day of all previous months including current month, upto a specified month. For example, I need last days of september, aug, july, june, may, april, march, feb, jan, dec 2015 like so:

temptable_mytable:

last_day_of_month
-----------------
2016-09-30
2016-08-31
2016-07-31
2016-06-30
2016-05-31
2016-04-30
2016-03-31
2016-02-30
2016-01-31
2015-12-31

I need to specify the month and year to go back to - in above case it's December 2015, but it could also be September 2015 and such. Is there a way that I can do a loop and do this instead of having to calculate separately for each month end?

Upvotes: 0

Views: 1511

Answers (6)

Cato
Cato

Reputation: 3701

declare @LASTMONTH date = '2018-10-01';


WITH MTHS AS (
                SELECT dateadd(month,month(getdate()),dateadd(year,year(getdate()) - 1900, 0)) aday
                UNION ALL
                SELECT DATEADD(month,1,aday) from MTHS WHERE  aday <= @LASTMONTH
                ),
    LASTDAYS AS (SELECT DATEADD(day,-1,aday) finaldayofmonth from MTHS)     
select * from LASTDAYS

Here is a version that goes forward or backwards as appropriate

declare @LASTMONTH date = '2013-10-01';


WITH DIF AS (SELECT CASE WHEN 
                        YEAR(@LASTMONTH) * 12 + MONTH(@LASTMONTH) 
                        >= YEAR(GETDATE()) * 12 + MONTH(getdate()) THEN 1 ELSE -1 END x),
MTHS AS (
                SELECT dateadd(month,month(getdate()),dateadd(year,year(getdate()) - 1900, 0)) aday 
                UNION ALL
                SELECT DATEADD(month,(SELECT X from dif),aday) from MTHS 
                        WHERE  month(aday)  !=  month(dateadd(month,1,@LASTMONTH)) or YEAR(aday) != YEAR(dateadd(month,1,@LASTMONTH))
                ),
    LASTDAYS AS (SELECT DATEADD(day,-1,aday) finaldayofmonth from MTHS)     
select * from LASTDAYS order by finaldayofmonth

Upvotes: 2

Peter Tirrell
Peter Tirrell

Reputation: 3003

This was cribbed together quick based on a couple different SO answers for the parts:

DECLARE @startdate datetime, @enddate datetime

set @startdate = '2015-12-01'
set @enddate = getdate()

;WITH T(date)
AS
( 
SELECT @startdate
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @enddate
)
SELECT DISTINCT
DATEADD(
    day, 
    -1,
    CAST(CAST(YEAR(date) AS varchar) + '-' + CAST(MONTH(date)AS varchar) + '-01' AS DATETIME))
FROM T OPTION (MAXRECURSION 32767);

Upvotes: 0

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

Use a recursive CTE with the EOMONTH function.

DECLARE @startdate DATE = '2016-01-01'

;WITH CTE
AS
(
    SELECT EOMONTH(GETDATE()) as 'Dates'
    UNION ALL
    SELECT EOMONTH(DATEADD(MONTH, -1, [Dates]))
    FROM CTE WHERE Dates > DATEADD(MONTH, 1, @startdate)
)

SELECT * FROM CTE

Upvotes: 4

djangojazz
djangojazz

Reputation: 13173

This should scale out no matter how far you go back or forward for your originating table or object.

SET NOCOUNT ON;

DECLARE @Dates TABLE ( dt DATE)

DECLARE @Start DATE = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
DECLARE @End DATE = DATEADD(YEAR, 1, @Start)

WHILE @Start <= @End
BEGIN
    INSERT INTO @Dates (dt) VALUES (@Start)

        SELECT @Start = DATEADD(DAY, 1, @Start)
END


; With x as 
    (
    Select 
        dt
    ,   ROW_NUMBER() OVER(PARTITION BY DATEPART(YEAR, Dt), DATEPART(MONTH, Dt) ORDER BY Dt Desc) AS rwn
 From @Dates
)
Select *
From x
WHERE rwn = 1
ORDER BY Dt

Upvotes: 1

Balan
Balan

Reputation: 421

with temp as (select -1 i union all select i+1 i from temp where i < 8) select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+i*-1,0)) from temp

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147374

Here's one approach, using a CTE to generate a list of incrementing numbers to allow us to then have something to select from and use in a DATEADD to go back for the appropriate number of months.

Typically, if you're doing this quite frequently, instead of generating numbers on the fly like this with the CROSS JOIN, I'd recommend just creating a "Numbers" table that just holds numbers from 1 to "some number high enough to meet your needs"

DECLARE @Date DATE = '20151201'
DECLARE @MonthsBackToGo INTEGER
SELECT @MonthsBackToGo = DATEDIFF(mm, @Date, GETDATE()) + 1;

WITH _Numbers AS
(
SELECT TOP (@MonthsBackToGo) ROW_NUMBER() OVER (ORDER BY o.object_id) AS Number
    FROM sys.objects o
        CROSS JOIN sys.objects  o2
)

SELECT  EOMONTH(DATEADD(mm, -(Number- 1), GETDATE())) AS last_day_of_month
FROM _Numbers

Upvotes: 1

Related Questions