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

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

Luuk123
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)
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
Flowing Fount of Yak Knowledge

3706 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

Luuk123
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  
 New 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.03 seconds. Powered By: Snitz Forums 2000