Post reply

In The Beginning

  • November 25, 2014 at 12:59 pm

    #1760898

    My favourite question in a long time! Thanks!

  • November 25, 2014 at 2:00 pm

    #1760911

    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;

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 25, 2014 at 4:15 pm

    #1760944

    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).

  • November 25, 2014 at 9:54 pm

    #1760964

    Good to know, nice question & explanation.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • November 25, 2014 at 11:27 pm

    #1760973

    Nice question, Thanks for sharing

  • November 26, 2014 at 5:26 am

    #1761058

    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.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 26, 2014 at 7:16 am

    #1761081

    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

  • November 26, 2014 at 7:45 am

    #1761092

    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

  • November 26, 2014 at 7:52 am

    #1761095

    Easy one, thanks.

  • December 1, 2014 at 2:53 pm

    #1761881

    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 39 (of 39 total)

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