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

sumesh gravatar image

sumesh
1 1 1 1

(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

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(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

Benjamin gravatar image

Benjamin
320 2 3 4

(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,(@vRemainder)/(@vMinuterMs))
ELSE CONVERT(char,(@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,(@vRemainder)/(1000))
ELSE CONVERT(char,(@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

Drikus gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1840
x28

asked: Oct 15, 2009 at 08:12 AM

Seen: 8208 times

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