Need help with recurring date - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with recurring date

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources