I'm looking for a query which selects a date in every month between a begin and end-date. For example: I want 15-01-2012, 15-02-2012 and 15-02-2012 as my result.
I constructed this query: ;with months (date) AS ( SELECT convert(datetime, '2013-01-31 00:00:00.000', 120) UNION ALL SELECT DATEADD(month,1,date) from months where DATEADD(month, 1, date) <= '2014-02-28 00:00:00.000' ) select date from months option (maxrecursion 0)
This works not 100%, because when the startdate is 31-01-2012, february has only 28 days and returns 28-02-2012. This is good, but the query results 28-03-2012 for march, and so on.
When the startdate is 31-01-2012 the query has to return all the last days of the months.
declare @date datetime;
set @date = '20130131';
declare @months int;
set @months = 10;
;with cte as
select @date as Date, 1 as N
select dateadd(mm,N,@date), N+1 from cte
where N < @months
) select Date from cte;