Build date table

Question

Build date table

Paul Peeters

SSC Enthusiast

Points: 127
More actions
December 8, 2010 at 8:58 pm

#230410

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

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

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

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: 5276 More actions · Answer 2

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 More actions · Answer 3

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: 7639 More actions · Answer 4

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: 259085 More actions · Answer 5

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: 95409 More actions · Answer 6

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: 64780 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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: 11358 More actions · Answer 10

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 SSCarpal Tunnel Points: 4962 More actions · Answer 11

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: 3757 More actions · Answer 12

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 More actions · Answer 13

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: 64780 More actions · Answer 14

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/