Build date table

  • Paul Peeters

    SSC Enthusiast

    Points: 127

    Comments posted to this topic are about the item Build date table

  • UMG Developer

    SSChampion

    Points: 13482

    Interesting question, I got it right by process of elimination. (Too lazy to do the math tonight.)

    However, I probably wouldn't use that method to populate a date table. The numbers table or a cross join are probably faster. (And I think easier.)

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

    This example shows how to quickly populate a table with successive dates? Not really, it shows a pretty convoluted way of doing it, not quick. Any time there is a while loop in T-SQL script, such a script is probably not efficient because SELECT is already a loop, and any attempts to use the slow while loop instead of a native select typically lead to performance issues. How about using a simple select, i.e.

    declare @dtFrom datetime;

    declare @dtTo datetime;

    declare @diff int;

    select

    @dtFrom = '2010-01-01T00:00:00.000',

    @dtTo = '2010-12-31T00:00:00.000',

    @diff = datediff(day, @dtFrom, @dtTo) + 1;

    create table #Dates

    (

    [Date] datetime not null primary key

    );

    -- this happily inserts dates exactly between first and last date, no more and no less

    insert into #Dates

    select top (@diff)

    dateadd(day, row_number() over (order by [object_id]) - 1, @dtFrom)

    from sys.all_columns;

    select * from #Dates;

    drop table #Dates;

    Oleg

  • vk-kirov

    SSCertifiable

    Points: 7686

    Very interesting technique.

    But inserting extra data and then deleting them is not the best solution, because delete operations are expensive. It's better to add a condition in order to insert only necessary data:

    INSERT INTO #Dates

    (

    [Date],

    [SomeValueForDate]

    )

    SELECT DATEADD(DAY, @n, [Date]) AS [Date],

    @n AS [SomeValueForDate]

    FROM #Dates

    WHERE DATEADD(DAY, @n, [Date]) <= @dtTo -- No need to delete data after that

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Nice question, thanks also Oleg's script looks great. Here is something that has the same result:

    DECLARE @dtFrom DATETIME

    DECLARE @dtTo DATETIME

    SET @dtFrom = '2010-01-01T00:00:00.000'

    SET @dtTo = '2010-12-31T00:00:00.000'

    DECLARE @Diff INT

    SET @Diff = DATEDIFF(DAY, @dtFrom, @dtTo)

    CREATE TABLE

    #Dates

    (

    [Date] DATETIME NOT NULL PRIMARY KEY,

    [SomeValueForDate] INT NOT NULL

    )

    CREATE TABLE #Digits(digit INT NOT NULL PRIMARY KEY);

    INSERT INTO #Digits(digit)

    VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

    SELECT DATEADD( DAY, D3.digit * 100 + D2.digit * 10 + D1.digit , @dtFrom) AS n

    FROM

    #Digits AS D1

    CROSS JOIN #Digits AS D2

    CROSS JOIN #Digits AS D3

    WHERE

    D3.digit * 100 + D2.digit * 10 + D1.digit <= @Diff

    ORDER BY n;

    DROP TABLE #Digits

    DROP TABLE #Dates

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Good question, but an even better follow-up discussion.

    Thanks for the code examples!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • WayneS

    SSC Guru

    Points: 95342

    While reading through this, I kept asking myself "What is the author trying to teach?". Unfortunately, I never did get a good answer to my question...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    WayneS (12/9/2010)


    While reading through this, I kept asking myself "What is the author trying to teach?". Unfortunately, I never did get a good answer to my question...

    He did tell us in the expanation: "This example shows how to quickly populate a table with successive dates."

    Unfortunately, this is not the best method.

    Fortunately, several better alternatives have been included in the discussion, so the objective of the author is achieved anyway 😉


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    I don't like questions like this, and not because I got it wrong.

    It teaches me nothing about Sql Server or T-SQL (except debugging peculiar T-SQL), and to be able to answer it correctly, without cheating, you will have to manually write down how many rows are inserted in each run of the loop in order to keep track of the number in the meaningless "SomeNumberForDate" column.

    64% got it right. Yeah, right!! How many cheated and ran the T-SQL code?

  • jts2013

    Hall of Fame

    Points: 3226

    I think as long as people learn does it matter if they ran the code? 🙂

    This WHILE was very convoluted so I ran the code to see if I could understand what it was doing and looked at the values in #Dates before the DELETE.

    So I hope I learnt that a) there are ways of populating test data quickly and b) not to use complicated WHILE loops!

  • Toreador

    SSChampion

    Points: 11242

    Nils Gustav Stråbø (12/9/2010)


    to be able to answer it correctly, without cheating, you will have to manually write down how many rows are inserted in each run of the loop in order to keep track of the number in the meaningless "SomeNumberForDate" column.

    I got it right by immediately eliminating the 1 and 365 options, then I couldn't spot any syntax errors, so went for the one that was left.

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    Nils Gustav Stråbø (12/9/2010)


    64% got it right. Yeah, right!! How many cheated and ran the T-SQL code?

    I cheated and got it right!

    By the way, how many of you read the forum comments and stop after you get to Hugo's? 🙂

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3754

    kevin.l.williams (12/9/2010)


    Nils Gustav Stråbø (12/9/2010)


    64% got it right. Yeah, right!! How many cheated and ran the T-SQL code?

    I cheated and got it right!

    By the way, how many of you read the forum comments and stop after you get to Hugo's? 🙂

    Kevin gets a 'like'

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Nils Gustav Stråbø (12/9/2010)


    I don't like questions like this, and not because I got it wrong.

    It teaches me nothing about Sql Server or T-SQL (except debugging peculiar T-SQL), and to be able to answer it correctly, without cheating, you will have to manually write down how many rows are inserted in each run of the loop in order to keep track of the number in the meaningless "SomeNumberForDate" column.

    64% got it right. Yeah, right!! How many cheated and ran the T-SQL code?

    I'd like the link that tells us we can't run the t-sql before answering. I can't remember ever seeing that post anywhere.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    kevin.l.williams (12/9/2010)


    By the way, how many of you read the forum comments and stop after you get to Hugo's? 🙂

    :laugh:

    If that number is significantly large, I'll have to consider postponing my comments, so that other valuable comments get read as well. 🙂

    Thanks for the kind words!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 28 total)

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