Dates between two dates for re-occuring events

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)

Dates between two dates for re-occuring events

Author

Topic

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2011-12-20 : 07:24:05

Hi,
We have a calendar feature,where user can select start_date and end_date for the event and select the event as re-occuring, so we need to get the re-occuring dates between two dates , event can be re-occure daily basis,weekly ,monthly and yearly .

Need help to write query which can give me dates between two dates (start and end date)
say,if user creates an event as re-occuring weekly basis today 12/20/2011 and the start date is 12/20/2011 and end date is 01/04/2012, the query should return
12/27/2011
01/04/2012

Any help is appricated.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-20 : 07:47:44

select dateadd(week,number,@start_date) as dates from master..spt_values
where type='p' and dateadd(week,number,@start_date) between @start_end and @end_date

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 08:59:02

if you dont want to rely upon spt_values table you can use below logic too


;With Dates(DateVal)
AS
(
SELECT @Start_date
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Dates
WHERE DATEADD(dd,1,DateVal)<=@end_date
)

SELECT *
FROM Dates
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-12-20 : 09:07:21

quote:
Originally posted by visakh16

if you dont want to rely upon spt_values table you can use below logic too

;With Dates(DateVal)
AS
(
SELECT @Start_date
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Dates
WHERE DATEADD(dd,1,DateVal)<=@end_date
)

SELECT *
FROM Dates
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

At expense of bad performance...

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources