Post reply

Creating Rows Between Dates In A Single SQL Statement

  • April 21, 2015 at 1:10 pm

    #317544

    Hi,

    I am trying to find an easy way to create multiple of just two date in a single sql statement.

    E.G.

    A statement using the parameters

    @StartDate = '2015-01-01'

    @EndDate = '2015-01-05'

    Ends up with rows:

    '2015-01-01'

    '2015-01-02'

    '2015-01-03'

    '2015-01-04'

    '2015-01-05'

    What would be the best way to do this ?

  • April 21, 2015 at 1:32 pm

    #1792553

    declare

    @StartDate date = '2015-01-01',

    @EndDate date = '2015-01-05'

    ;

    SELECT dateadd(DAY, x.rn, @StartDate)

    FROM

    ( SELECT (ROW_NUMBER() OVER(ORDER by object_id))-1 AS rn FROM sys.objects) AS x

    WHERE

    x.rn <= DATEDIFF(DAY,@StartDate,@enddate )

    ;

  • April 21, 2015 at 4:00 pm

    #1792584

    Another perfect situation for a tally table. You can create an in-line table using CROSS JOINs or create a physical tally table and use that. Unfortunately I can't post a cross join because my work server thinks it's some type of "attack" SQL apparently, but the basic SQL with an assumed-to-exist tally CTE/table is:

    SELECT DATEADD(DAY, tally - 1, @StartDate)

    FROM tally

    WHERE tally BETWEEN 1 AND DATEDIFF(DAY, @StartDate, @EndDate) + 1

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • April 22, 2015 at 2:53 am

    #1792638

    Thanks guys

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply