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..
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 )