Using Date Types In Warehouses

Koen Verbeeck argues that date keys in warehouses should be actual date types:

The worst are by far the string representation, as there is no actual check on the contents. It can literally contain everything. And is ’01/02/2018′ the first of February 2018 (like any sane person would read, because days come before months), or the 2nd of January? So if you have to store dates in your data warehouse, avoid strings at all costs. No excuses.

The integer representation – e.g. 20171208 – is really popular. If I recall Kimball correctly, he said it’s the one exception where you can use smart keys, aka surrogate keys that have a meaning embedded into them. I used them for quite some time, but I believe I have found a better alternative: using the actual date data type.

I bounce back and forth, but I’m sympathetic to Koen’s argument, which you can read by clicking through.

Related Posts

T-SQL Tuesday 118 Roundup

Kevin Chant played Santa Claus this month: I hope they had as much fun contributing their posts as I had reading them afterwards. For those who missed the invitation you can read about it here. For some reason I thought it’d be a good idea to do it on my birthday month when I first given […]

Read More

PARSE, CAST, and CONVERT

Max Vernon gives us three ways to change data types: PARSE provides a mechanism to convert a wide variety of character based dates into a datetime data type. From the Docs: Returns the result of an expression, translated to the requested data type in SQL Server. Use PARSE only for converting from string to date/time and number […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728