"SQL Spackle" is a collection of short articles written based on multiple requests for similar code or to share short methods for getting certain things done. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks."

--Phil McCracken

Introduction

This is a very old subject that has been covered many times but, according to the number of recent posts asking for such a thing, I thought I’d put my own article out there for folks to find. I’ve also done things in a way that I’ve not seen others use so I thought I’d share that with you, as well.  And, no, I've not tried the FORMAT function of SQL Server 2012, yet.

Microsoft introduced the DATE, TIME, and DATETIME2 data-types in SQL Server 2008 much to almost everyone’s delight. The "new" data-types can be quite efficient for storage because the number of bytes required to store them varies depending on the desired precision. They’re also a delight to the scientific and other communities because the range of dates (although I believe it uses the wrong calendar prior to the year 1753) has been expanded to include as far back as January 1, 1 AD not to mention that the accuracy has been increased to 100ns instead of the 3.3ms long suffered by DATETIME.

For most of us, though, we don’t need such a large range of dates nor do we need 100ns accuracy. For most of us, calculating the difference between two DATETIMEs is sufficient because we normally only need accuracy to the second or even minute. For most of us, being able to calculate differences (durations) with an accuracy of 3.3 milliseconds is a bonus rather than an inaccuracy especially if we're calculating things like "hours worked" and payroll.

Along with the relatively new date and time data-types, a capability for simplicity in the calculation of the difference between two dates has been lost. Such calculations have become comparatively complex and are well beyond the scope of this particular article.

The purpose of this short article is to demonstrate how simple it is to calculate duration between two dates (and times) using the DATETIME data-type and also covers a super simple method to display the difference in extended hours, minutes, seconds, and milli-seconds.

Calculating the Difference (Duration) Between Two DATETIMES

Let’s get right to it. Run the following code, which creates a start and end date and calculates the duration between them…

--===== Create start and end dates for testing purposes.
     -- "DT" is an abbreviation for "DATETIME", not "DATE"
DECLARE  @StartDT DATETIME
        ,@EndDT   DATETIME
;
 SELECT @StartDT = '2000-01-01 10:30:50.780'
        ,@EndDT  = '2000-01-02 12:34:56.789'
;
--===== Display the dates and the duration
 SELECT  StartDT  = @StartDT
        ,EndDT    = @EndDT
        ,Duration = @EndDT-@StartDT
;

… and you’ll get the following results.

StartDT EndDT Duration
----------------------- ----------------------- -----------------------
2000-01-01 10:30:50.780 2000-01-02 12:34:56.790 1900-01-02 02:04:06.010

There are a couple of things to notice here. First, all we did was subtract the start date from the end date to calculate the duration. You can only do that with the DATETIME and SMALLDATETIME data-types. It doesn’t work for any of the newer date and time data-types. If you do, you’ll get an error message similar to the following if you try (I used DATETIME2() for this error).

Msg 8117, Level 16, State 1, Line 12

Operand data type datetime2 is invalid for subtract operator.

The second thing to notice is that, compared to the original data, the milli-seconds have been calculated incorrectly. According to the original data, the milliseconds should have been 789-780 = 009. So what happened there? If you look at the output for EndDT, you'll notice that the 789 was rounded up to the nearest 3.3 milliseconds or 790. It’s not a fault. It’s what the accuracy of DATETIME is. So the difference was correctly calculated as 790-780 = 010.

Minutes and seconds were calculated correctly and, if you consider only a 24 hour clock, hours was also calculated correctly. But that’s the problem with this method. How would most people know that "1900-01-02" actually represents 24 hours giving us, along with the 02 hours displayed, a total of 26 hours? It would also be nice to actually see the duration as 26:04:06:010, which would be the very human-readable HH:MI:SS:mmm format. We’d also like to handle just about any number of hours that the INT data-type could handle.

Quickly Formatting the Duration

If you’re not familiar with the CONVERT function, take a half-hour out, right now, to read about it in "Books Online". You can get there by pressing the {f1} key while in SSMS. Then do an "Index" lookup on "CONVERT function". This is a short "Spackle" article so I’m not going to cover all there is about CONVERT here.

Do the same for the STUFF and DATEDIFF functions.

One of the formats that CONVERT allows is format 114, which looks like HH:MI:SS:mmm and is a 24 hour format ("Military Time" to some). As you saw before, the minutes, seconds, and milliseconds are auto-magically displayed correctly. It’s the hours that cause us some pain because of the "wrap around" back to 00 after we pass 23:59:59:997.

The following formula takes care of that little problem. It uses the 114 format and then STUFFs the first two characters (the wrap-around hours) out of existence and replaces them with the actual difference in hours. 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. All in all, that’s a whole lot easier than trying to parse out the individual components of time, doing some calculations, and the trying to concatenate all the parts, including the colons, all back together again.

Including the previous test data, here’s what we end up with. Like I said, the formula is really simple.

--===== Create start and end dates for testing purposes.
     -- "DT" is an abbreviation for "DATETIME", not "DATE"
DECLARE  @StartDT DATETIME
        ,@EndDT   DATETIME
;
 SELECT @StartDT = '2000-01-01 10:30:50.780'
        ,@EndDT  = '2000-01-02 12:34:56.789'
;
--===== Display the dates and the desired format for duration
 SELECT  StartDT  = @StartDT
        ,EndDT    = @EndDT
        ,Duration = STUFF(CONVERT(VARCHAR(20),@EndDT-@StartDT,114),1,2,DATEDIFF(hh,0,@EndDT-@StartDT))
;

Here are the results from that bit of computational heaven.

StartDT EndDT Duration
----------------------- ----------------------- ------------
2000-01-01 10:30:50.780 2000-01-02 12:34:56.790 26:04:06:010

As you can see, we’ve easily broken and formatted the 24 hour barrier.

Performance and "Range"

There are a whole lot of methods for calculating and displaying the difference between two DATETIMEs but you have to be careful. The ones that calculate the difference in milliseconds or even seconds are quite limited in range. The formula we just used in the previous section will handle up to 3ms less than 10,000 years because of the limits (9999-12-31 23:59:59.997) of CONVERT and DATEDIFF. You can see that in the following code.

--==== This works. 
SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' 
AS DATETIME)-CAST('1900-01-01 00:00:00.000' AS DATETIME),114) 
; 
--==== Adding just 3ms more duration causes CONVERT to fail. 
SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' 
AS DATETIME)-CAST('1899-12-31 23:59:59.997' AS DATETIME),114) 
; 

What about performance? You can also see that in the following code. Check the "Messages" tab in SSMS after you run the following code for the time it took to do this calculation on a million row table. As always, comments on how things are working are included in the code. I’ve sorted the output by Duration just to make it easier to see what’s going on.

--===== Create a million row test table of start and end dates
     -- where the end date is always later than the start date.
     -- In this case, there could be a full century of time between the dates.
     IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL DROP TABLE #JBMTest;
   WITH cteRandomStartDT AS
(
 SELECT TOP 1000000
        StartDT = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME)
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
 SELECT StartDT
        ,EndDT = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2100') + StartDT
   INTO #JBMTest
   FROM cteRandomStartDT
;
--===== Calculate the duration for each start/end date pair in the table.
     -- I'm dumping the output to a variable to take display times out of the picture.
     -- The formula calculates the total difference in time, converts that to HH:MI:SS:mmm format,
     -- and then stuffs the first 2 characters out and replaces them with difference in time
     -- measured in whole hours.
DECLARE @BitBucket VARCHAR(20);
    SET STATISTICS TIME ON;
 SELECT @BitBucket = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))
   FROM #JBMTest;
    SET STATISTICS TIME OFF;
GO
--===== Just to show that it really does work...
 SELECT *
        ,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))
   FROM #JBMTest
  ORDER BY EndDT-StartDT
;

When I run that code on my laptop, it takes about 10 seconds for the whole shebang to execute including the construction and display of a million row table. The code that actually calculates the difference (duration) between two dates and formats it for human consumption only takes 717ms for the million rows.

Epilogue

Calculating the difference (duration) between two dates and times is an age-old problem but has a simple solution that’s nearly as old as the original problem. If you can tolerate the 3.3ms accuracy and rounding provided by the DATETIME data-type, the duration calculation is incredibly simple. Just subtract the start date and time from the end date and time.

The hard part is the formatting of the hours and thanks to three simple functions, CONVERT, STUFF, and DATEDIFF with a "0" offset, that’s no longer difficult, either.

Thanks for listening folks,

"Crack Filled!"

© Copyright – Jeff Moden - 15 October 2013 – All Rights Reserved