Modern Datatime Addition

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Comments posted to this topic are about the item Modern Datatime Addition

  • Jeff Moden

    SSC Guru

    Points: 994238

    BWAAAA-HAAAA-HAAAAA!!!  "Modern"?  It may be more recent but it sure as hell isn't more "Modern".  "Crippled" is a much better word!

    Technically, the given answer is incorrect because you can, indeed, add dates and times if you use the correct datatype.  For example, all of the following work just fine...

    --===== Add a time to a date 
    DECLARE @MyDate DATETIME = '2015-08-27'
    ,@MyTime DATETIME = '15:33:21.057';
    SELECT MyDateTime1 = @MyDate + @MyTime
    GO
    --===== Add a literal time to a DATETIME (SomeStartDateTime+Duration=EndDatetime)
    DECLARE @MyDate DATETIME = '2015-08-27';
    SELECT MyDateTime1 = @MyDate + '15:33:21.057';
    GO

    Heh... "Modern DateTime Addition" (which is what I think you really intended for the title)... another fine example that "Change is inevitable... change for the better is not".  MS screwed the pooch when they made the DATE, TIME, and DATETIME datatypes because they no longer follow ANSI standards, which state things like EndDateTime-StartDateTime = Duration and StartDateTime+Duration = EndDateTime.

    Without using DATEDIFF_BIG (which MS finally added later added because of all the bitching people were doing after MS screwed up the functionality of the newer datatypes), trying doing the same as below with the DATETIME2 datatype and see what I mean.

    --===== Calculate the duration as hhhhh:mi:ss.mmm
    DECLARE @StartDT DATETIME = '2000-01-01 10:30:50.780'
    ,@EndDT DATETIME = '2000-02-02 12:34:56.787'
    ;
    SELECT Duration = STUFF(CONVERT(VARCHAR(20),@EndDT-@StartDT,114),1,2,DATEDIFF(hh,0,@EndDT-@StartDT))
    ;

    Of course, MS never made a format that would handle more than 23 hours like what is rendered above.  MS did, however, make a mistake with DATETIME by basing the time portion on the number of 300ths of a second instead of true decimal time and so it always rounds to millisecond values where the last digit will always end with 0, 3, and 7.  That's the ONLY advantage that DATETIME2 has over the DATETIME datatype but, compared to the other missing functionality of the DATETIME2 datatype, it's a trivial annoyance.  Let's also mention that dates prior to 01 Jan 1753 (the lower limit for DATETIME) for DATETIME2 are incorrect depending on when individual countries adopted the Gregorian calendar.

    And, if you want a real fun exercise, try converting the difference between two dates and times to Decimal hours (a very common request) using the DATETIME2 datatype. Using the DATETIME datatype, the exercise is trivial, as follows...

    --===== Calculate the duration in decimal hours
    DECLARE @StartDT DATETIME = '2000-01-01 10:30:50.780'
    ,@EndDT DATETIME = '2000-02-02 12:34:56.787'
    ;
    SELECT DecimalHours = CONVERT(DECIMAL(9,1),@EndDT-@StartDT)
    ;

    If you really want "modern" time calculations, they need to change dates and times to the correct underlying decimal values AND make direct temporal math a possibility (as it already is in DATETIME) as well as adding some temporal formatting to support the various outputs required to calculate duration AND add functionality for correctly calculating things like age in years, months, and days, etc.

    So, a more proper answer to the given question should be, "You can't do this because MS screwed up the DATETIME2 datatype".

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Ray Herring

    SSCertifiable

    Points: 5205

    So Jeff, do you have an opinion on Datatime addition ?

  • Jeff Moden

    SSC Guru

    Points: 994238

    Ray Herring wrote:

    So Jeff, do you have an opinion on Datatime addition ?

    Heh... ya think? 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71310

    DATETIME2 - what a mission.

    agree with Mr Moden about the poor pooch on this one...

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

  • Ragnar

    Newbie

    Points: 3

    Technically, the given answer is incorrect not only because, as Jeff Moden said, you can add dates and times if you use the correct datatype, but also because you simply cannot use the addition operator on DATETIME2 data types regardless of what values are contained.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Technically, because the question casts as DATETIME2, it is no incorrect. Please don't say that. The point was that you cannot add datetime2 values.

  • Jeff Moden

    SSC Guru

    Points: 994238

    Heh... technically, the question answer is still incorrect because you CAN add dates and times... if the answer was that you cannot add DATETIME2s, then the answer would have been technically correct.

    The reason I'm making a rub about this is if someone that doesn't know better reads the answer, the may go on thinking that it's impossible to add dates and times for everything and that's just not true.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Sue_H

    SSC Guru

    Points: 89970

    The answer is pretty clear (and correct) when I read it:

    Explanation

    This is similar to another question (https://www.sqlservercentral.com/questions/get-the-datetime), but this time an error is returned. Addition is not allowed with the datetime2 type.

    Ref: datetime2 - https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017

    Sue

  • Ragnar

    Newbie

    Points: 3

    The explanation is correct, the answer "Returns an error since we cannot add dates and times" is not.

  • Jeff Moden

    SSC Guru

    Points: 994238

    Post deleted... misread a response.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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