Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Luuk123
Yak Posting Veteran
52 Posts |
Posted - 2013-06-13 : 10:31:15
|
Hi all,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.Can anybody help me?thanks! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-13 : 10:46:20
|
Do it like this:declare @date datetime;set @date = '20130131';declare @months int;set @months = 10;;with cte as( select @date as Date, 1 as N union all select dateadd(mm,N,@date), N+1 from cte where N < @months) select Date from cte; |
|
|
Luuk123
Yak Posting Veteran
52 Posts |
Posted - 2013-06-13 : 13:58:12
|
Thanks! That did the job! |
|
|
|
|
|
|
|