Shifting gears back to the original subject... IF you're using the DATETIME datatype, you can really cheat on this one. As a bit of a sidebar, there's great utility in being able to do direct addition and subtraction of dates and times. I'll never understand why MS didn't include such a simple capability with the DATE and TIME datatypes. If they ever change that for the DATETIME datatype, there will be some high velocity porkchops in some designer's future. 😛
Details are in the code below and I've created a test table in case folks want to do performance testing or "effective range" testing. Solutions with a DATEDIFF in milliseconds have their limits.
--===== 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 Delta-T 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 *
,DeltaT = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))
FROM #JBMTest
ORDER BY EndDT-StartDT
;
--Jeff Moden
Change is inevitable... Change for the better is not.