March 07, 2010

Generate a Start and End Date Range using T-SQL




I was generating a date range by specifying a Start and End Date and an interval. I wanted the following output with an interval of 3 days (including the start date):

StartDate EndDate

2010-01-01 00:00:00.000 2010-01-03 00:00:00.000
2010-01-04 00:00:00.000 2010-01-06 00:00:00.000

and so on..

Here’s the query (thanks toTGBraitman) to generate the range:

DECLARE
@StartDate datetime = '2010-01-01',
@EndDate datetime = '2010-03-01',
@interval smallint = 3

;WITH CTE as
(
SELECT @StartDate as StDt
UNION ALL
SELECT DATEADD(day, @interval, StDt)
FROM CTE
WHERE DATEADD(day, @interval, StDt) <= @EndDate
)

SELECT StDt as StartDate,
DATEADD(day, @interval-1, StDt) as EndDate
FROM CTE

OUTPUT

Generate Date Range T-SQL


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Generate a Start and End Date Range using T-SQL"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions