Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with recurring date
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

52 Posts

Posted - 06/13/2013 :  10:31:15  Show Profile  Reply with Quote
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)
SELECT convert(datetime, '2013-01-31 00:00:00.000', 120)
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?


James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 06/13/2013 :  10:46:20  Show Profile  Reply with Quote
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;
Go to Top of Page

Yak Posting Veteran

52 Posts

Posted - 06/13/2013 :  13:58:12  Show Profile  Reply with Quote
Thanks! That did the job!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000