Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Useful Dates: The Many Uses of Date Tables Expand / Collapse
Author
Message
Posted Wednesday, December 17, 2008 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2014 1:45 PM
Points: 4, Visits: 12
Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:

select dateadd(dd,datediff(dd,0,getdate()),0)

Enjoyed the article
Post #621274
Posted Wednesday, December 17, 2008 8:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:00 PM
Points: 4,438, Visits: 6,344
Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and powerful.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #621302
Posted Wednesday, December 17, 2008 8:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
rkonopka (12/17/2008)
Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:

select dateadd(dd,datediff(dd,0,getdate()),0)

Enjoyed the article


Yes... and faster to boot.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #621303
Posted Wednesday, December 17, 2008 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
TheSQLGuru (12/17/2008)
Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and powerful.


I was thinking the very same thing, Kevin... my first impression was "What, are you kidding me? How'z that gonna work?" Then I remembered the saying I have framed in my cube... "Before you can think outside the box, you must first realize... you're in a box!" So, I'm gonna find out, "How'z that gonna work" because like Sergiy says, "A Developer must not guess... a Developer must KNOW!" Because of the denormalized state of the table, it could be very fast...

... it'll take a bit to do the kind of testing I'm thinking of...


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #621333
Posted Wednesday, December 17, 2008 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 3, 2011 8:48 AM
Points: 26, Visits: 71
Joe Celko (12/17/2008)
The calendar date is the natural key, so the magically vague "id" is not needed. The table is not normalized to 1NF -- you have data and metadata in it. What do the NULLs mean?


The reason that I added the date_id column is to allow the dimension to be referenced by a 4-byte value instead of an 8 byte value if the application calls for linking data to a coordinate.
Post #621374
Posted Wednesday, December 17, 2008 9:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 3, 2011 8:48 AM
Points: 26, Visits: 71
mike.byrnes (12/17/2008)
If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.


I guess I was incorrect that it's 500KB per year then because here is the space used by my table.

name rows reserved data index_size unused
-------------------- ----------------- -------- ---------- ------------------
util_time_coordinate 1829625 99792 KB 52400 KB 47296 KB 96 KB

Post #621381
Posted Wednesday, December 17, 2008 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 3, 2011 8:48 AM
Points: 26, Visits: 71
Jeff Moden (12/17/2008)
Brandon,

Not sure what the text editor you used may have done to you, but there's a lot of missing spaces in your code... for example CREATE TABLE has become CREATETABLE and SET @variable has become SET@variable. There are many other accidental concatenations throughout the code. It might be why there's not much discussion or rating on this article, so far... people just don't wanna take the time to fix posted code.

The "Text Editor" I used was SQL Server Management Studio for all SQL Code. The tested and working code was then copied into the article which is a word document typed in OpenOffice. I then emailed the entire DOC file to Steve so it is something in the publishing that is causing it.

Also, you may want to add a link to your article on how to build your numbers table... it would help people that just don't know about such things, a lot. Either that, or it's short enough to just use in the code.

Good idea. When I submitted this one, the first hadn't been published yet for me to include the link. I will have to go back and do it now.


Contact Steve Jones for how to edit your article if you're interested in doing such a thing. It's not difficult and Steve is very happy to help on these things.

Great idea and I will do that and fix them both up because this happened with the first one too.
Post #621389
Posted Wednesday, December 17, 2008 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 3, 2011 8:48 AM
Points: 26, Visits: 71
rkonopka (12/17/2008)
Instead of double converting (first to varchar then back to datetime), wouldn't it be simpler and "cleaner" to do this:

select dateadd(dd,datediff(dd,0,getdate()),0)

Enjoyed the article


I've started to use that approach for time stripping but this article was written before I adopted that method. Thanks for the input!
Post #621391
Posted Wednesday, December 17, 2008 11:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:00 PM
Points: 4,438, Visits: 6,344
CheeseMan316 (12/17/2008)
mike.byrnes (12/17/2008)
If my calculations are correct, then if each year took 5Mb then 200 years would be 1 Gb worth of data, not 100Mb.


I guess I was incorrect that it's 500KB per year then because here is the space used by my table.

name rows reserved data index_size unused
-------------------- ----------------- -------- ---------- ------------------
util_time_coordinate 1829625 99792 KB 52400 KB 47296 KB 96 KB



It is customary and useful to have your int date keys be formatted CCYYMMDD, at least in the classical date dimension scenario. Identities are really not helpful here.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #621495
Posted Wednesday, December 17, 2008 1:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
I've worked with a dates table to find "missed" days, and also used CTES to generate sets of dates on the fly using a tally table to increment some number of days from a given starting date. Obviously having a single date column and a single clustered index require less disk space, and the cte approach required next to nothing in disk, although you pay for it in CPU cycles. But your table extends the concept to hours, months, and quarters. Could you give us a real world example or two using other dimensions besides day?


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #621584
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse