Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, January 18, 2014 12:40 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, April 21, 2015 7:08 PM Points: 190, Visits: 448

#### More DateTime info...

This time I teased out the internal representation of the DateTime data type. First I ran the following:
`Declare @d DateTime;Set @d='1753-01-01 00:00:00.000';Select Cast(@d as VarBinary),Cast(@d as BigInt);`

0xFFFF2E4600000000 | -53690

Immediately I noticed that 0xFFFF2E4600000000 is not equivalent to -53690! In fact -53690 is the decimal representation of the first 4 hexadecimal bytes! The Cast to VarBinary seemed to know that @d was 8 bytes long, why didn't the Cast to BigInt have the same length info? So then I tried this:
`Declare @d DateTime;Set @d='1753-01-01 00:00:00.000';Select Cast(@d as VarBinary),Cast(@d as BigInt),Cast(Cast(@d as VarBinary) as BigInt);`

0xFFFF2E4600000000 | -53690 | -230596794122240

Now that's more like it. 0xFFFF2E4600000000 is equivalent to -230596794122240! Next, based on a hunch from a previous post on this thread I ran:
`Declare @d DateTime;Set @d='1900-01-01 00:00:00.000';Select Cast(@d as VarBinary),Cast(@d as BigInt),Cast(Cast(@d as VarBinary) as BigInt);`

0x0000000000000000| 0 | 0

It looks like the internal representation is broken down in the following manor:
Bytes 1 - 4 represent the date component (CCYY-MM-DD)
Bytes 5-8 represent the time component (HH:MM:SS.mmm)

But what are their units and intervals? Well, the following query will tell us that:
`Declare @d DateTime;Set @d='1753-01-01 00:00:00.000';Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);Set @d='1753-01-02 00:00:00.001';Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);Set @d='1753-01-02 00:00:00.003';Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);`

0xFFFF2E4600000000 | -53690 | 0
0xFFFF2E4700000000 | -53689 | 0
0xFFFF2E4700000001 | -53689 | 1

Notice how I modified the date-time string before each query and notice the resultant output values. From an analysis of these values it can be seen that:
Bytes 1 - 4 represent the date component in days, however, the range does not start with 0, it starts with -53690.
Bytes 5-8 represent the time component in 3.33~ millisecond counts, i.e., this count increments by 1 for every 3.33~ milliseconds that pass; the range starts at 0.

So what are the actual ranges for days and 3.33~ millisecond counts? Lets run another query:
`Declare @d DateTime;Set @d='1753-01-01 00:00:00.000';Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);Set @d='9999-12-31 23:59:59.997';Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);`

0xFFFF2E4600000000 | -53690 | 0
0x002D247F018B81FF | 2958463 | 25919999

From these results we can see that the day range is -53690 through 2958463, or 3012154 (don't forget to add 1 for the zero crossing) which is 8246 years, the full span from years 1753 to 9999.
From these results we can also see that the 3.33~ millisecond count range is 0 through 25919999, or 25920000 counts which is 86400000 milliseconds (25920000*3.33~) or 1 days worth of milliseconds.

So, there you have it. You could use this knowledge to craft an ElapsedTime function that will work across the full spectrum of time from 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997 with some reasoned logic and judicious use of SubString, Cast, & Convert. It probably won't be the fastest performer but it will give you the full time span available without any "out-of-range datetime value" exceptions!

Happy Casting!

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1532357
 Posted Saturday, January 18, 2014 7:42 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 2:03 AM Points: 3,829, Visits: 9,931
 Jeff Moden (1/16/2014)Eirikur Eiriksson (1/16/2014)Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always greater than the start time. This is the reason why I prefer to use double cast, first to varbinary and then to bigint. `SELECT StartDT ,EndDT ,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT)) ,DurationI = STUFF(CONVERT(VARCHAR(20),StartDT-EndDT,114),1,2,DATEDIFF(hh,0,StartDT-EndDT)) ,cast(cast(StartDT as varbinary(128)) as bigint) As StartTicks ,cast(cast(EndDT as varbinary(128)) as bigint) AS EndTicks ,cast(cast(StartDT as varbinary(128)) as bigint)-cast(cast(EndDT as varbinary(128)) as bigint) as XmY ,cast(cast(EndDT as varbinary(128)) as bigint)-cast(cast(StartDT as varbinary(128)) as bigint) as YmXFROM #JBMTest;`You're correct and I could have added a simple statement, such as "Assuming two non-null dates where the StartDT is less than or equal to the EndDT,...", to make sure that no one would have to assume.Shifting gears to the code you posted...While I very much appreciate alternate methods for just about everything, I have to admit that the code you wrote for the StartTicks and EndTicks columns has me confused. Why are they labeled as "Ticks"? Because DATETIME is based on two integers where one integer represents the number of whole days and the other represents the number of 1/300ths of a second that have passed since midnight, I don't understand where the word "Ticks" comes into play. Without further explanation or esoteric knowledge, one might think that you were implying "clock ticks" and, without some additional calculation, they are not. Further, since the maximum hex value of the "Time" portion for a DATETIME datatype is 018B81FF (23:59:59.997) and falls drastically short of the FFFFFFFF value before the rollover to the next int (day), the VARBINARY/BIGINT converted value can't represent "ticks" of equal duration of any kind. A simple conversion of DATETIME to FLOAT would get you a lot closer to some form of "ticks" value.This is the reason why I prefer to use double cast, first to varbinary and then to bigint. I'm even more confused after that statement. Please explain how your code, as it is posted, ensures that the StartDT and EndDT values are in the proper order even if they were to produce the required human-readable output (and it IS required by most). From what I can see, the code doesn't resolve that problem. Unless I'm missing something, it simply presents two columns where an additional decision would still need to be made as to whether or not the two DATETIMEs were in the correct order or not.And, of course, although most would never approach the limit and as you have yourself recognized, your conversions do have a limit at 148 years whereas the code in the article does not.I must be missing something. Please explain.First let me apologize for the inaccuracy and incompleteness of my initial postings and for implying that a direct cast of the DATETIME to BIGINT would hold any sensible meaning. To finish what I started;DATETIME is a structure of two integers, signed integer for the number of days starting at -53690 for 1753-01-01 which are the number of days until 1900-01-01.For this reason, the subtraction of two equal datetime values returns 1900-01-01.It can hold 3012153 days or 2958463 days after 1900-01-01 which is 9999-12-31.And as you correctly said, the second part of the DATETIME structure holds the time from midnight in 1/300th parts of a second, that is 25920000 intervals per day. This makes 78,075,031,679,999 (1/300th) parts for the full day range of the DATETIME, well within the limits of the BIGINT, 2^63-1 (9,223,372,036,854,775,807). Since the storage size is the same for both data types, I prefer to store both date-time and duration/period as (1/300th) parts BIGINT if to be used for temporal calculations.The calculation is quite quick and straight forward; extract the day part, add 53690, then multiply by 25920000 and add the time part. To extract the day value one can either shift the bytes by division of 0xFFFFFFFF or using SUBSTRING, the latter tends to be faster. For the time part OR or XOR with an integer of 0 (zero).A sample test code snip using the #JBMTest;`DECLARE @BitBucket BIGINT; SET STATISTICS TIME ON; SELECT @BitBucket = ABS( ((CAST((53690 + CAST(SUBSTRING(CAST(EndDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000) + (CAST(EndDT AS VARBINARY(8)) ^ 0)) - ((CAST((53690 + CAST(SUBSTRING(CAST(StartDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000) + (CAST(StartDT AS VARBINARY(8)) ^ 0)) ) FROM #JBMTest; SET STATISTICS TIME OFF;GO`A MIN-MAX demonstration`DECLARE @MinDT DateTime;DECLARE @MaxDT DateTime;SELECT @MinDT ='1753-01-01 00:00:00.000', @MaxDT ='9999-12-31 23:59:59.998';SELECT @MinDT AS MinDateTime ,@MaxDT AS MaxDateTime ,ABS( ((CAST((53690 + CAST(SUBSTRING(CAST(@MaxDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000) + (CAST(@MaxDT AS VARBINARY(8)) ^ 0)) - ((CAST((53690 + CAST(SUBSTRING(CAST(@MinDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000) + (CAST(@MinDT AS VARBINARY(8)) ^ 0)) ) AS TP_300 ,ABS( ((CAST((53690 + CAST(SUBSTRING(CAST(@MaxDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000) + (CAST(@MaxDT AS VARBINARY(8)) ^ 0)) - ((CAST((53690 + CAST(SUBSTRING(CAST(@MinDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000) + (CAST(@MinDT AS VARBINARY(8)) ^ 0)) ) / 25920000 AS NumberOfDays;`Again my apologies for the inaccuracy of my previous postings.
Post #1532376
 Posted Monday, January 20, 2014 6:03 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, January 29, 2014 5:36 AM Points: 2, Visits: 16
 Thanks for the article. I appreciate it.One question though. It said in the article It’s important that the DATEDIFF in the formula have a "starting date" of "0" (the numeric equivalent of 1900-01-01) and an "ending date" of the duration calculation so that we don’t introduce any errors simply by crossing an "hour boundary" at the wrong time.which I expect means the DATEDIFF section should use DATEDIFF(hh,0,@EndDT-@StartDT) rather than DATEDIFF(hh,@StartDT,@EndDT)which also returns 26 in this example. I guess I don't understand what an "hour boundary" is or why we need to guard against crossing one. Could you give an example where these two DATEDIFF statements would give different results?
Post #1532591
 Posted Monday, January 20, 2014 8:11 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:40 AM Points: 7,162, Visits: 15,022
 When the hour increments from one value to the next, or from 12 to 1, an hour boundary is said to have been crossed. The same happens for the other time portions too. Try this code:SELECT *, [Hours] = DATEDIFF(hour, Starttime,EndTime), [Minutes] = DATEDIFF(minute, Starttime,EndTime), Seconds = DATEDIFF(second, Starttime,EndTime), [millisecond] = DATEDIFF(millisecond, Starttime,EndTime), [microsecond] = DATEDIFF(microsecond, Starttime,EndTime)FROM ( SELECT Starttime = cast('20140120 16:59:59.997' AS DATETIME), EndTime = cast('20140120 17:00:00.000' AS DATETIME)) dThere's only 3ms difference between Starttime and EndTime, but an hour boundary was crossed (16:nn:nn to 17:nn:nn), a minute boundary was crossed (16:59:nn to 17:00:nn) and a second boundary was crossed (16:59:59 to 17:00:00). “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1532669
 Posted Monday, January 20, 2014 1:57 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, January 29, 2014 5:36 AM Points: 2, Visits: 16
 Thanks Chris, that helps a lot! So I'm understanding now that starting at 0 and ending at the difference [ie. DATEDIFF(hh,0,@EndDT-@StartDT)] allows the query to work when the hour part of @StartDT is larger than the hour part of @EndDT.
Post #1532778
 Posted Monday, January 20, 2014 6:14 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, April 21, 2015 7:08 PM Points: 190, Visits: 448

#### DateDiff tricks and the missing 147 years

Data-type DateTime has the following properties:

It can represent any valid date/time between 1753-01-01 00:00:00.000 and 9999-12-31 23:9:59.998.

`Declare	@st DateTime,	@et DateTime;Select	@st='1753-01-01 00:00:00.000',	@et='9999-12-31 23:59:59.998';`

Mess with the dates a bit an you will see that these two are the upper and lower boundaries.
Nothing new here, BOL tells us that.

It is internally represented by two 32-bit integers.

`Declare	@st DateTime,	@et DateTime;Select	@st='1753-01-01 00:00:00.000',	@et='9999-12-31 23:59:59.997';Select	Cast(@st as VarBinary)[@st as Hex],	Cast(@et as VarBinary)[@et as Hex];Select		SubString(Cast(@st as VarBinary),1,4)[@st 1st Int as Hex],	SubString(Cast(@st as VarBinary),5,4)[@st 2nd Int as Hex],	SubString(Cast(@et as VarBinary),1,4)[@et 1st Int as Hex],	SubString(Cast(@et as VarBinary),5,4)[@et 2nd Int as Hex]Select		Cast(SubString(Cast(@st as VarBinary),1,4) as Int)[@st 1st Int as Int],	Cast(SubString(Cast(@st as VarBinary),5,4) as Int)[@st 2nd Int as Int],	Cast(SubString(Cast(@et as VarBinary),1,4) as Int)[@et 1st Int as Int],	Cast(SubString(Cast(@et as VarBinary),5,4) as Int)[@et 2nd Int as Int];`

Oddly, the lowest date/time boundary (1753-01-01 00:00:00.00) is not internally code as two zeros. It is -53690 and 0. Also, notice that the highest date/time first integer is 2958463. In passing the second integer really has no bearing on my subject but I'll tell you that it stores the number of 3.33~ millisecond ticks since midnight for a given day.

Now run this:

`Select	DateAdd(dd,53690,'1753-01-01 00:00:00.000');`

And we get 1900-01-01 00:00:00.000. This is our reference point around which the first of the two internal integers is composed; it represent the arithmetic difference in days between 1900-01-01 00:00:00.000 and your other date/time value. This is why, internally, it can be negative or positive. This is also the source of another odd T-SQL quirk (like this is the first one).

`Declare	@st DateTime,	@et DateTime;Select	@st='1753-01-01 00:00:00.000',	@et='9999-12-31 23:59:59.998';Select	DateDiff(yy,@st,@et);`

You get back 8246. No surprises here, this is just the number of years between our valid lowest and highest dates. Now run:

`Declare	@st DateTime,	@et DateTime;Select	@st='1753-01-01 00:00:00.000',	@et='9999-12-31 23:59:59.998';Select	DateDiff(yy,0,@et-@st);`

Huh, the dreaded Arithmetic overflow error converting expression to data type datetime. But if you run:

`Declare	@st DateTime,	@et DateTime;Select	@st='1900-12-31 00:00:00.000',	@et='9999-12-31 23:59:59.998';Select	DateDiff(yy,0,@et-@st);`

Everything is ok, you get 8099, the number of years between those two date/time values.

So what happened here that we apparently lost 147 years of range (1900-1753) by using the DateDiff function in this manor? Well, the DateDiff function wants a DateTime value as it's second and third parameters so it can do the difference calculation with the knowledge of its internal days representation. When you pass 0 as the second parameter you are thwarting this process by calculating the difference yourself and possibly getting caught by the zero crossing problem!

Example:

1753-01-01 is internally represented by a days number of -53690 (the smallest valid days number)

1900-01-01 is internally represented by a days number of 0

9999-01-01 is internally represented by a days number of 2958463 (the largest valid days number)

Ex.1) If you take the arithmetic difference (using the binary minus operator) between 9999-01-01 and 1900-01-01 as days you'd get 2958099

Ex.2) If you take the arithmetic difference (using the binary minus operator) between 9999-01-01 and 1753-01-01 as days you'd get 3012153

Now, I'm going to tell you that this problem actually has nothing to do directly with the DateDiff function! When you take the arithmetic difference of two DateTime values the result is implicitly cast back into a DateTime value. Therefore, Ex.1 above would have no problem being passed into DateDiff because it will cast back into a DateTime data type. However, Ex.2 runs into the Arithmetic overflow error converting expression to data type datetime because 3012153 is larger than the highest internal days number!

So, we can see that DateDiff knows how to handle the difference calculation when one of its DateTime values has a negative representation, where an arithmetic difference doesn't! So, should we avoid using DateDiff in this manor? Of course not! Its solves some rounding problems for us when we do! Just use it with an awareness of what is going on under the covers!

Your 147 years have been returned!

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1532831
 Posted Wednesday, April 8, 2015 11:05 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 12:37 PM Points: 1,049, Visits: 2,533
 Thanks, Jeff, very useful! I ran in to the datetime2 issue just yesterday while trying what turned out to be an unnecessary modification to my audit system. It's kind of odd: I don't recall doing a lot of datediff work over the years until this job where we need awareness of expired eye exams and such. -----Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Post #1675484
 Posted Thursday, April 9, 2015 4:55 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 1:25 PM Points: 4,278, Visits: 454
 Thanks everyone for all the good comments and ideas.
Post #1675987
 Posted Wednesday, April 15, 2015 9:04 AM
 SSC Journeyman Group: General Forum Members Last Login: Yesterday @ 5:53 AM Points: 75, Visits: 221
 The (very small) fly in the ointment is that the 114 date style code in the CONVERT function returns milliseconds preceded by a colon rather than a period (decimal point).That's always looked odd to my eyes.Microsoft's definition of valid string literals for the time datatype includes the following:Milliseconds can be preceded by either a colon (:) or a period (.). If a colon is used, the number means thousandths-of-a-second. If a period is used, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.BOL gives the 114 format as hh:mi:ss:mmm, and indeed three digits are always returned for milliseconds, with leading zeros if needed.That effectively makes the colon and period interchangeable, so I struggle to see the point of the 113 and 114 codes.Do they relate to any ANSI standard?
Post #1677321
 Posted Thursday, April 16, 2015 4:11 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 10:30 PM Points: 37,455, Visits: 34,317
 Gary Harding (4/15/2015)The (very small) fly in the ointment is that the 114 date style code in the CONVERT function returns milliseconds preceded by a colon rather than a period (decimal point).That's always looked odd to my eyes.Microsoft's definition of valid string literals for the time datatype includes the following:Milliseconds can be preceded by either a colon (:) or a period (.). If a colon is used, the number means thousandths-of-a-second. If a period is used, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.BOL gives the 114 format as hh:mi:ss:mmm, and indeed three digits are always returned for milliseconds, with leading zeros if needed.That effectively makes the colon and period interchangeable, so I struggle to see the point of the 113 and 114 codes.Do they relate to any ANSI standard?My greatest disappointment is that MS doesn't have a datetime conversion for Pig Latin. --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." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1677807

 Permissions