Amit Ranjan Amit Ranjan - 6 months ago 26
SQL Question

tsql: How to retrieve the last date of each month between given date range

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

You can use CTE for getting all last days of the month within the defined range

Declare @Start datetime
Declare @End datetime

Select @Start = '20130808'
Select @End = '20131111'
;With CTE as
(
Select @Start  as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
UNION ALL
Select Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE
Where Date<@End
)

Select * from CTE
where [Last]=1   OPTION ( MAXRECURSION 0 )