Modern Datatime Addition

Question

Modern Datatime Addition

Steve Jones - SSC Editor

SSC Guru

Points: 735513
More actions
August 9, 2019 at 12:00 am

#3663128

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

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

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

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

Ray Herring SSCertifiable Points: 5533 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

Stewart "Arturius" Campbell SSC Guru Points: 72596 More actions · Answer 4

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 More actions · Answer 5

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: 735513 More actions · Answer 6

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: 1004151 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

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

Sue_H SSC Guru Points: 90860 More actions · Answer 8

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 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

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