DATEDIFF using integer keys

  • Matthew Cushing

    SSCrazy

    Points: 2898

    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.

  • Thom A

    SSC Guru

    Points: 98515

    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.

  • Matthew Cushing

    SSCrazy

    Points: 2898

    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: 98515

    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.

  • Matthew Cushing

    SSCrazy

    Points: 2898

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

    Thanks for the info!

  • gvoshol 73146

    Hall of Fame

    Points: 3144

    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

    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: 76707

    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:

    1. 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.
    2. 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

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

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