question

sumesh avatar image
sumesh asked

Time Difference in SQL Server 2008

To obtain a formatted value in hh:mm for time difference between two time values.

PS: the two time field are datatype time and the db is SQLSERVER2008

sql-server-2008time
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered
select  convert(time, dateadd(minute, datediff(minute, tm1, tm2), 0))            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Benjamin avatar image
Benjamin answered

I chose to keep the calculation to seconds to preserve accuracy but then CHOP off the unneeded seconds. Also, if you can bend your requirement to include hh:mm:ss then you can eliminate the LEFT function that I used to drop the seconds.

--here is the answer

SELECT            
    [EventName],            
    [StartTime],            
    [EndTime],            
    LEFT(            
    	CONVERT(varchar, DATEADD(ss, DATEDIFF(ss, [StartTime], [EndTime]),0),108)            
    	,5) --use the LEFT,5 to drop the seconds portion, otherwise remove if hh:mm:ss is okay            
    	AS [TimeDiff (hh:mm)]            
FROM [dbo].[Events]            

--here is an end-to-end working example
--this example only works with SQL Server 2008 and above

CREATE TABLE [dbo].[Events] (            
    [EventName] varchar(30) NULL,            
    [StartTime] time NULL,            
    [EndTime] time NULL )            

--add some values

INSERT INTO [dbo].[Events]            
VALUES            
('First Event', '08:00', '08:30'),            
('Second Event', '09:00', '10:00'),            
('Third Event', '15:00', '16:30')            

--view the records with the time difference

SELECT            
    [EventName],            
    [StartTime],            
    [EndTime],            
    LEFT(            
    	CONVERT(varchar, DATEADD(ss, DATEDIFF(ss, [StartTime], [EndTime]),0),108)            
    	,5) --use the LEFT,5 to drop the seconds portion, otherwise remove if hh:mm:ss is okay            
    	AS [TimeDiff (hh:mm)]            
FROM [dbo].[Events]            

--clean up from our test

DROP TABLE [dbo].[Events]            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Drikus avatar image
Drikus answered

I log elapsed processing time for an application's calculation, which allows me to have a ms precision of the 24 day: 86399999ms on a 64-bit machine.

Working with TIME in SQL 2008 is markedly easier than SQL 2000.

-- Declare variables            
DECLARE @vTime1 TIME(3), @vTime2 TIME(3)            
SELECT @vTime1 = '00:00:00.000', @vTime2 = '23:59:59.997';            
            
SELECT CONVERT(TIME, DATEADD(ms, DATEDIFF(ms, @vTime1, @vTime2), 0));            

Because this is up to ms precision the rounding off (last 3ms) sets the largest lower boundary value to 997ms.

To trudge through TIME calculations manually, consider the TSQL script below, which gives you a nice result in either CHAR(12) or TIME to the last ms, i.e. 999. Don't ask me why I had the energy to write this piece of code...

-- Declare variables            
DECLARE @vTime1 TIME(3), @vTime2 TIME(3);            
            
DECLARE @vTotalTimeMs INT            
        , @vHourMs INT            
        , @vMinuterMs INT            
        , @vSecondsMs INT            
        , @vMsMs INT            
        , @vHours TINYINT            
        , @vRemainder INT            
        , @vSecondsDivisor TINYINT            
        , @vTimeDiff VARCHAR(16)            
        , @vMsValue CHAR(3);            
-- Initialise variables            
SELECT @vTime1 = '00:00:00.000', @vTime2 = '23:59:59.999';            
            
SET @vHourMs = 3600000;            
SET @vSecondsDivisor = 60;            
SET @vMinuterMs = (@vHourMs/@vSecondsDivisor);            
SET @vSecondsMs = (@vMinuterMs/@vSecondsDivisor);            
SET @vMsMs = (@vSecondsMs/1000);            
            
SELECT @vTotalTimeMs = DATEDIFF(ms,@vTime1,@vTime2);            
SELECT @vHours = DATEDIFF(ms,@vTime1,@vTime2)/@vHourMs;            
            
-- Main Query            
-- Hour            
SET @vTimeDiff =    CASE    WHEN @vHours < 10             
                            THEN '0'+ CAST(@vHours AS CHAR(1))            
                            ELSE CAST(@vHours AS CHAR(2))            
                    END +':'            
SELECT @vTotalTimeMs,@vRemainder,@vHourMs, @vTimeDiff;            
SELECT @vRemainder = @vTotalTimeMs % @vHourMs;  --<-- Total time expressed in ms, smallest denominator                              
            
-- Minute            
SELECT @vTimeDiff = @vTimeDiff +    CASE    WHEN ((@vRemainder)/(@vMinuterMs)) < 10            
                                            THEN '0'+CONVERT([char](1),(@vRemainder)/(@vMinuterMs))            
                                            ELSE CONVERT([char](2),(@vRemainder)/(@vMinuterMs))            
                                    END +':'             
SELECT @vTotalTimeMs,@vRemainder,@vMinuterMs, @vTimeDiff;                                               
            
-- Adjust remaining time            
SET @vRemainder = (@vRemainder%@vMinuterMs);            
            
-- Seconds              
SELECT @vTimeDiff = @vTimeDiff +    CASE    WHEN (@vRemainder)/(1000) < 10            
                                            THEN '0'+CONVERT([char](2),(@vRemainder)/(1000))            
                                            ELSE CONVERT([char](2),(@vRemainder)/(1000))            
                                            END +'.'                
SELECT @vTotalTimeMs,@vRemainder,@vSecondsMs, @vTimeDiff;                                                               
-- Adjust remaining time            
SET @vRemainder = (@vRemainder%@vSecondsMs);            
SELECT @vTotalTimeMs,@vRemainder,@vMsMs, @vTimeDiff;            
            
-- Milli second            
SET @vMsValue = CAST((@vRemainder)/(@vMsMs) AS CHAR(3));            
SELECT @vTimeDiff = @vTimeDiff +    CASE    LEN(@vMsValue)            
                                            WHEN 1  THEN '00'+@vMsValue            
                                            WHEN 2  THEN '00'+@vMsValue            
                                            ELSE @vMsValue            
                                    END            
SET @vRemainder = (@vRemainder%@vMsMs);                                                     
SELECT @vTotalTimeMs,@vRemainder,@vMinuterMs, @vTimeDiff;            
            
-- Result            
SELECT CAST(RTRIM(LTRIM(@vTimeDiff)) AS TIME(7)) AS [Time Differential];            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.