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


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

No comments: