DATEDIFF using integer keys

Question

DATEDIFF using integer keys

Matthew Cushing

SSCrazy

Points: 2898
More actions
May 16, 2017 at 2:15 pm

#336879

I've always wondered why you can't use datediff with two dates that are in int format.
When I run select datediff(day, '20170516', '20170618') I get 33, but when I use a database field, it bombs out on me. Isn't there an easier way to see how many days fall between two dimDate datekeys than converting them both?
These work:
datediff(day, '20170516', '20170618') as ViableDays
datediff(day, CONVERT(VARCHAR(10), invDateKey, 112), CONVERT(VARCHAR(10), expDateKey, 112)) as ViableDays
This doesn't:
datediff(day, invDateKey, expDateKey) as ViableDays
seems like you should be able to compare these keys without converting them.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

That's because dates are technically stored as numbers ,a value of 0 is 1900-01-01, 1 is 1900-01-02. Having a value of 20170516 would be 20,170,516 days after 1900-01-01. Considering that there's 365.25 days in a year... That's (approximately) 55,233 years, 313 days. So, entering an integer value of 20170516 is like entering the date 57133-11-10. That's a LONG time from now ;).

So your example above, using integers, would really be:
DATEDIFF(DAY, 42869, 42902)
(try running that, it works, and gives the results 33)

If you're storing dates, store them as dates, simply put. It makes things a lot easier. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 2

I figured it had something to do with that, thanks for clarifying. A followup if you don't mind. In the past, I've seen if you find a date, replace it with an int equivalent, but the more I work with it, the more I'm getting away from that. Even with Kimball's book, I found dates being somewhat of an exception as far as whether to store them as dimDate key vs original date.

Thom A SSC Guru Points: 99126 More actions · Answer 3

What you seem to be talking about there is more ofa data warehouse point of view, rather than a how you would store "normal" data. Data warehouses and "normal" data are stored and represented very differently, and it's important to remember that. Data warehouses are made up of fact and dimension tales, and fact tables are often simply a series of keys and data to aggregate. This isn't how you should store your normal day to day data. (I say this VERY loosely, those who know better than I!)

Having a DimDate with an INT datekey is quite common, and yes you reference that date key in your other tables. The important things to remember then is, however, that you ensure you query and use the date value in your DimDate table, not the datekey in your fact table. In very simple terms:
SELECT MAX(DATEDIFF(DAY, OD.DateValue, DD.DateValue)) AS MaxDaysToDispatch FROM FactOrders FO JOIN DimDate OD ON FO.OrderDateKey = OD.DateKey JOIN DimDate DD ON FO.DispatchDateKey = DD.DateKey;
Doing this with your integer values would not be as simple. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 4

Sorry, i mentioned dimDate, but I should have mentioned it was all DWH.

Thanks for the info!

GaryV Hall of Fame Points: 3307 More actions · Answer 5

Matthew Cushing - Tuesday, May 16, 2017 2:15 PM
I've always wondered why you can't use datediff with two dates that are in int format.
When I run select datediff(day, '20170516', '20170618') I get 33, but when I use a database field, it bombs out on me. Isn't there an easier way to see how many days fall between two dimDate datekeys than converting them both?
These work:
datediff(day, '20170516', '20170618') as ViableDays
datediff(day, CONVERT(VARCHAR(10), invDateKey, 112), CONVERT(VARCHAR(10), expDateKey, 112)) as ViableDays
This doesn't:
datediff(day, invDateKey, expDateKey) as ViableDays
seems like you should be able to compare these keys without converting them.

Not to pile on, but please note that in your first datediff example, you are not sending integers. You are sending strings that contain an integer value. SQL knows how to turn string values into real dates, and thus the datediff works.
Try your third example ("This doesn't") by casting the date keys to varchar, just to see that it works. But you probably don't want to rely on that implicit conversion from varchar to date.

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 6

Agreed and thank you. I figured it was because essentially it was a string, but wanted to confirm. Figured there would be a way to do this, but all I've been finding is cast or convert.

drew.allen SSC Guru Points: 76944 More actions · Answer 7

gvoshol 73146 - Wednesday, May 17, 2017 7:09 AM
Not to pile on, but please note that in your first datediff example, you are not sending integers. You are sending strings that contain an integer value. SQL knows how to turn string values into real dates, and thus the datediff works.
Try your third example ("This doesn't") by casting the date keys to varchar, just to see that it works. But you probably don't want to rely on that implicit conversion from varchar to date.

Two things:

Strings contain strings. PERIOD. An implicit or explicit conversion needs to be performed in order for a string to be interpreted as another data type. This may seem pedantic, but the point is that SQL is strongly typed.
He is sending strings that can be interpreted as either an integer or a date. Because DATEADD requires a date/time data type, the string is interpreted as a date not an integer.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA