Fun Data Arithmetic

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    Comments posted to this topic are about the item Fun Data Arithmetic

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Nice one, thanks Steve

    SQL Server stores these as integers, so the values are converted to integers, added, and then cast back to datetime. 

    Strange values for a strange holiday.

    Actually, not so strange...
    DATETIME has "1900-01-01 00:00:000" as a starting point (stored as 0).
    therefore, 2000-01-01 - 1900-01-01 = 100 years.
    The rest, as they say, is history...😎

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • ildjarn.is.dead

    Right there with Babe

    Points: 773

    I think it stores the dates as floats, not ints.

  • HappyGeek

    SSCoach

    Points: 18684

    Good question thanks Steve.

    ...

  • Gareth Davison

    Right there with Babe

    Points: 723

    ildjarn.is.dead - Tuesday, October 31, 2017 1:12 AM

    I think it stores the dates as floats, not ints.

    According to the documentation datetime is:

    Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

    I agree, that although there is a logical explanation it is strange behaviour. Behaviour that has been fixed with the DATETIME2 data type. Using this would type instead would yield a sensible error of:

    Operand data type datetime2 is invalid for add operator.


  • George Vobr

    SSCrazy Eights

    Points: 9247

    Thanks for this strange question. I had a bit of fun with this code:

    DECLARE @dt1 DATETIME = '2017-10-31',
            @dt2 DATETIME = '2000-01-01',
            @dt_def DATETIME = '1900-01-01' -- DATETIME default value;

    SELECT DATEADD(dd, DATEDIFF(dd, @dt_def, @dt1) + DATEDIFF(dd, @dt_def, @dt2), @dt_def);

  • Jeff Moden

    SSC Guru

    Points: 997103

    ildjarn.is.dead - Tuesday, October 31, 2017 1:12 AM

    I think it stores the dates as floats, not ints.

    I used to think so as well but, with the help of a friend on another site, I proved that what Books Online days about 2 integers being used to store a date/time is absolutely spot on.  Also, the integer that stores time stores the number of 1/300ths of a second since midnight of the given day and that's why the DATETIME datatype has a resolution of 3.3ms and is rounded during display to the nearest ms, which also means the last digit of ms will always be 0, 3, or 7.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 997103

    GD_515 - Tuesday, October 31, 2017 2:10 AM

    ildjarn.is.dead - Tuesday, October 31, 2017 1:12 AM

    I think it stores the dates as floats, not ints.

    According to the documentation datetime is:

    Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

    I agree, that although there is a logical explanation it is strange behaviour. Behaviour that has been fixed with the DATETIME2 data type. Using this would type instead would yield a sensible error of:

    Operand data type datetime2 is invalid for add operator.


    DATETIME2 fixed nothing and I avoid it like the plague because it's actually broken.  It no longer allows direct temporal math and now violates the ANSI requirements of StartDate+Interval = EndDate and EndDate-StartDate = Interval.

    Microsoft was forced into inadvertently admitting that they had made a huge mistake by coming out with DATEDIFF_BIG to make up for the mistake instead of fixing the real problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Gareth Davison

    Right there with Babe

    Points: 723

    Jeff Moden - Tuesday, October 31, 2017 8:54 AM

    DATETIME2 fixed nothing and I avoid it like the plague because it's actually broken.  It no longer allows direct temporal math and now violates the ANSI requirements of StartDate+Interval = EndDate and EndDate-StartDate = Interval.

    While there may be issues with DATETIME2, such as requiring an explicit DATEADD or DATEDIFF, for the scenarios described, I disagree that preventing someone adding two dates together is not a fix.

    2017-10-31 + 2000-01-01 doesn't equal 2117-10-31 any more than Tuesday + Tuesday = Thursday.

  • Jeff Moden

    SSC Guru

    Points: 997103

    GD_515 - Tuesday, October 31, 2017 9:20 AM

    Jeff Moden - Tuesday, October 31, 2017 8:54 AM

    DATETIME2 fixed nothing and I avoid it like the plague because it's actually broken.  It no longer allows direct temporal math and now violates the ANSI requirements of StartDate+Interval = EndDate and EndDate-StartDate = Interval.

    While there may be issues with DATETIME2, such as requiring an explicit DATEADD or DATEDIFF, for the scenarios described, I disagree that preventing someone adding two dates together is not a fix.

    2017-10-31 + 2000-01-01 doesn't equal 2117-10-31 any more than Tuesday + Tuesday = Thursday.

    I do agree that adding two things that are obviously dates together may seem like a pitfall but limiting that would defeat the ability to do StartDate+Interval where the only current method of using a literal is to represent Interval is to list it as a date/time with an understand of what the offset from 1900-01-01 represents.

    That being said, I will agree to disagree with you. πŸ˜‰

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ed Wagner

    SSC Guru

    Points: 286985

    Having the ability to add an integer to a datetime works to the standard of StartDate + Interval.  The datetime2 type doesn't allow this simple operation.  The engine also does some other neat things with datetime under the hood to protect us from ourselves.  In spite of it's limitation of 1753, it works for most scenarios.  I'll stick with datetime because it just works.

    The real question is about the date data type.  Why doesn't StartDate + Interval work with a date?  It should simply add a number of days to StartDate and return a date, but doesn't.

  • TomThomson

    SSC Guru

    Points: 104773

    Ed Wagner - Wednesday, November 1, 2017 6:43 AM

    Having the ability to add an integer to a datetime works to the standard of StartDate + Interval.  The datetime2 type doesn't allow this simple operation.  The engine also does some other neat things with datetime under the hood to protect us from ourselves.  In spite of it's limitation of 1753, it works for most scenarios.  I'll stick with datetime because it just works.

    The real question is about the date data type.  Why doesn't StartDate + Interval work with a date?  It should simply add a number of days to StartDate and return a date, but doesn't.

    th only reason to use datetime2 is if either dates outside the range of datetime or precision to better than 3.3ms are needed.   I've done some computng where the better precision was needed,  and although that work didn't need a relational database (it was mathematical modelling of software performance in very high speed data communications systems) I can easily envisage wanting that precision in RDBMS. But I have never needed dates earlier that 1753 or later than 9999 in computing, and don't imagine there'll be a real need in the next couple of millennia.   And I most certainly have needed interval handling in databases.  It's a blasted disgrace that none of the shiny new datetime2 and date and time types provide any tidy handling of intervals,  a distinct step backward from datetime.

    Tom

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

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