x

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

more ▼

asked Oct 15, 2009 at 08:12 AM in Default

avatar image

sumesh
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

select  convert(time, dateadd(minute, datediff(minute, tm1, tm2), 0))            
more ▼

answered Oct 15, 2009 at 09:08 AM

avatar image

Squirrel 1
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left

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]            

more ▼

answered Oct 15, 2009 at 01:22 PM

avatar image

Benjamin
320 3 5 8

(comments are locked)
10|1200 characters needed characters left

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];

  • more ▼

    answered May 21, 2010 at 07:44 AM

    avatar image

    Drikus
    1

    (comments are locked)
    10|1200 characters needed characters left
    Your answer
    toggle preview:

    Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

    Follow this question

    By Email:

    Once you sign in you will be able to subscribe for any updates here

    By RSS:

    Answers

    Answers and Comments

    SQL Server Central

    Need long-form SQL discussion? SQLserverCentral.com is the place.

    Topics:

    x2076
    x35

    asked: Oct 15, 2009 at 08:12 AM

    Seen: 8792 times

    Last Updated: Oct 15, 2009 at 09:53 AM

    Copyright 2016 Redgate Software. Privacy Policy