Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2006-06-21 : 11:40:31
|
| I have a date column in my customer dimension: Last Contact Date.I will be reporting on it a lot.My gut feeling tells me I should Make this column an int and join to the dimTime table. But this is never done for time dimension columns in the adventureworks demo.Is there a trade-off if I use and int instead of a date column? I cant think of any... |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-21 : 16:35:43
|
| Use 0 to denote 1900101 for the int date key.That way conversions between int and datetime will be straightforward.cast(intDate as datetime) -- will give the correct "real" datedatediff(day,0,regularDate) -- will give the correct "int" date.The only tradeoffs is readability, and it can be argued that int is not the "correct" datatype.In date dimension tables I always keep both as unique (calendarID, calendarDate). Then I apply a constraint CHECK(CAST(calendarDate-calendarID as FLOAT)=0).Benefits the way I see it is: storage space, and also the column is automatically constrained in such a way that no time-portions can be entered.rockmoose |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-06-21 : 17:59:35
|
| very nice! that works a treat. cheers! |
|
|
|
|
|
|
Date or date key?