Get all dates between two dates

As for question if i want to get all dates between two dates. For example i want all dates between 01/jan/2010 and 31/Dec/2010 we need to write SQL Query as below:

DECLARE @StartDate DATETIME, @EndDate DATETIME   SELECT @StartDate = '20100101', @EndDate = '20101231'   SELECT DATEADD(day, z.num, @StartDate) FROM ( SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num FROM (SELECT 0 i UNION ALL SELECT 1) b0 CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1 CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2 CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3 CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4 CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5 CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6 CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7 CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8 CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9 CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10 ) z WHERE z.num <= DATEDIFF(day, @StartDate, @EndDate) ORDER BY z.num

The output will be as:

2 Comments

  • For SQL 2005 or higher, as long as there's less than 100 days in the range, you could also use a recursive CTE:

    WITH Days (Value) As
    (
    SELECT
    @StartDate

    UNION ALL

    SELECT
    DateAdd(day, 1, Value)
    FROM
    Days
    WHERE
    Value < @EndDate
    )
    SELECT
    Value
    FROM
    Days;

  • I wish to show the dates along-with data into a Gridview. Can anyone give me code in C# for asp.net?

Comments have been disabled for this content.