In The Beginning

  • Trevor Ball

    SSCommitted

    Points: 1854

    My favourite question in a long time! Thanks!

  • Ed Wagner

    SSC Guru

    Points: 286982

    kherald69 (11/25/2014)


    Ed Wagner (11/25/2014)


    PHYData DBA (11/25/2014)


    Brian.Klinect (11/25/2014)


    That's exactly what I did. I had no idea I could use a negative number for a datetime!

    Since DateTime is stored as an Integer, it only makes sense to use the Positive and Negative Values.

    Anything thing else would be wasteful.

    Actually, it's stored as two integers. The first one is the date and the second one is the time. I completely agree that not using the full range of the date integer would be wasteful. It's actually pretty efficient and works well.

    Actually, it is stored as a float. The whole number is the date and the fraction is the time.

    Then why does this work?

    WITH cte AS (

    SELECT GETDATE() date_format,

    CAST(GETDATE() AS Binary(8)) binary_format)

    SELECT date_format now_datetime,

    binary_format now_binary,

    SUBSTRING(binary_format, 1, 4) day_part_bin,

    SUBSTRING(binary_format, 5, 4) time_part_bin,

    CAST(SUBSTRING(binary_format, 1, 4) AS Integer) date_part_int,

    CAST(SUBSTRING(binary_format, 5, 4) AS Integer) time_part_int,

    DATEADD(DAY, CAST(SUBSTRING(binary_format, 1, 4) AS Integer), 0) today_no_time

    FROM cte;

  • kherald69

    Say Hey Kid

    Points: 695

    I can see that it works but what is the meaning of the time int value.

    Here is an example of why I say it is stored as a float.

    with cte as (

    select getdate() as date_format

    , cast(getdate() as float) as date_as_float)

    select date_format

    , date_as_float

    , floor(date_as_float) as date_int

    , date_as_float - floor(date_as_float) as time_fraction

    , floor((date_as_float - floor(date_as_float)) * 86400000) as time_in_ms

    from cte;

    If you take the time_in_ms and do the math you can get the hours, minutes, seconds and milliseconds (+/- a few ms).

  • Hany Helmy

    SSChampion

    Points: 13488

    Good to know, nice question & explanation.

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question, Thanks for sharing

  • Ed Wagner

    SSC Guru

    Points: 286982

    kherald69 (11/25/2014)


    I can see that it works but what is the meaning of the time int value.

    Here is an example of why I say it is stored as a float.

    with cte as (

    select getdate() as date_format

    , cast(getdate() as float) as date_as_float)

    select date_format

    , date_as_float

    , floor(date_as_float) as date_int

    , date_as_float - floor(date_as_float) as time_fraction

    , floor((date_as_float - floor(date_as_float)) * 86400000) as time_in_ms

    from cte;

    If you take the time_in_ms and do the math you can get the hours, minutes, seconds and milliseconds (+/- a few ms).

    The second integer is the number of 0.003 second timeslices since midnight.

  • TomThomson

    SSC Guru

    Points: 104773

    Carlo Romagnano (11/25/2014)


    Extremely easy!

    Thanks!

    But more than half the answers so far were wrong! (Probably because people forgot about negative integers, I guess.)

    Tom

  • TomThomson

    SSC Guru

    Points: 104773

    Kev T (11/25/2014)


    Thanks Paul, how interesting! I wonder how this is handled in Greece with regards to big data and historical records, or if they forgone the lost days somehow.

    I doubt if Greece has any problems when compared to many other places.

    For example in India the official calendars (note the plural) are (i) the Indian National Calendar (a lunar calendar) and (ii) the Gregorian Calendar, and neither is used for anything much by Indians, who mostly use the Vikram Samvat calendar (another lunar one, where most years are a day or two less than 360 days, but about every 5 or 6 years there will be a year 29 or 30 days longer than a typical year to bring the calendar back into line with the seasons).

    Israel uses both the Gregorian and the Hebrew calendar.

    There are at least 5 distinct calendars in use in the Muslim world (Hijri, Hijri-Shamsi, Oromo, Ethiopic, and Persian).

    And just to create some real fun, earlier this year the Orthodox Church of Poland switched back to the unrevised Julian Calendar, having since 1924 used the revised Julian calendar of 1923, while of course the Polish government is on the Gregorian calendar (presumably the Government and the Orthodox church will now celebrate Christmas on different days).

    Tom

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Easy one, thanks.

  • dbeyfyerd

    SSC Enthusiast

    Points: 137

    TomThomson (11/26/2014)


    Carlo Romagnano (11/25/2014)


    Extremely easy!

    Thanks!

    But more than half the answers so far were wrong! (Probably because people forgot about negative integers, I guess.)

    Forgot about, and hadn't used, but conceptually I would have missed it also. I would expect the beginning of time to be zero rather than the smallest date able to be entered. Like if height were stored as a float.

    But I learned something new... 🙂

Viewing 10 posts - 31 through 40 (of 40 total)

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