Friday, June 07, 2013

Microsoft SQL Select Date Range

Select an arbitrary range of dates in Microsoft SQL, which can be joined against data with date gaps using a LEFT OUTER JOIN on the date range table.

DECLARE @DateStart datetime = '01-01-2013';
DECLARE @DateEnd   datetime = DATEADD(day, 29, @DateStart);

WITH DateRange(Day) AS (
    SELECT    @DateStart AS [Day]
    UNION ALL
    SELECT    DateRange.Day + 1
    FROM      DateRange
    WHERE     DateRange.Day < @DateEnd
)
SELECT    CONVERT(date, DateRange.Day) AS [Date]
FROM      DateRange