tsql: How to retrieve the last date of each month

Question

I have two date for example 08/08/2013 and 11/11/2013 and I need last date of each month starting from August to November in a table so that i can iterate over the table to pick those dates individually.

I know how to pick last date for any month but i am stucked with a date range.

kindly help, it will be highly appreciated.

Note : I am using Sql 2008 and date rang could be 1 month , 2 month or 6 month or a year or max too..

Answer 1

I've created a table variable, filled it with all days between @startDate and @endDate and searched for max date in the month.

declare @tmpTable table (dates date)
declare @startDate date = '08/08/2013'
declare @endDate date = '11/11/2013'

while @startDate <= @endDate
begin
    insert into @tmpTable (dates) values (@startDate)
    set @startDate = DATEADD(DAY, 1, @startDate)
end

select max(dates) as [Last day] from @tmpTable as o
group by datepart(YEAR, dates), datepart(MONTH, dates)

Results:

Last day
2013-08-31
2013-09-30
2013-10-31
2013-11-11

To also get last day of November this can be used before loop:

set @endDate = DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @endDate) + 1, 0))

Answer 2

You can use a recursive CTE to do this, note the MAXRECURSION OPTION prevents an infinite loop:

DECLARE @StartDate DATE = '2013-08-08'
DECLARE @EndDate DATE = '2013-11-11'

;WITH dateCTE
AS
(
    SELECT CAST(DATEADD(M, 1,DATEADD(d, DAY(@StartDate) * -1, @StartDate)) AS DATE) EndOFMonth
    UNION ALL 
    SELECT CAST(DATEADD(M, 2,DATEADD(d, DAY(EndOFMonth) * -1, EndOFMonth)) AS DATE) 
    FROM dateCTE
    WHERE EndOFMonth < DATEADD(d, DAY(@EndDate) * -1, @EndDate)

)
SELECT *
FROM dateCTE
OPTION (MAXRECURSION 30);

This returns

EndOFMonth
----------
2013-08-31
2013-09-30
2013-10-31

Answer 3

The following CTE gives you the last day of every month from February 1900 until the middle of the 26th century (on my machine):

;with LastDaysOfMonths as (
    select DATEADD(month,
                 ROW_NUMBER() OVER (ORDER BY so.object_id),
                 '19000131') as Dt
    from sys.objects so,sys.objects so1
)
select * from LastDaysOfMonths

It should be easy enough to use it as part of a larger query or to filter it down to just the dates you want. You can adjust the range of years as needed by changing the constant 19000131. The only important thing to do is make sure that you use a month that has 31 days in it and always have the constant be for day 31.