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
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
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]
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];
No one has followed this question yet.