Calculate time difference in hours minutes and seconds with SQL Server?

How to calculate time difference in hours, minutes and seconds with SQL Server? The SQL Server function DATEDIFF() allows us to calculate the difference between two timestamps, but only in one unit. For example, only seconds, or only minutes or only hours. So calculating the difference between a start date and end date is not straightforward with SQL Server. This post shows you how to do so with a simple example query, explained step by step.

  

As an example, this simple SQL query is using DATEDIFF() with two timestamps having exactly one hour of difference. And every query gives only one unit. So basically, we must combine or concatenate several queries to get the hours, minutes and seconds within one unique SQL query. Below each query is returning one unit.

-- Declare and initialize two variables with exactly one hour of difference: 9 - 8 = 1h 
DECLARE @DateStart 	DATETIME
DECLARE @DateEnd 	DATETIME
SET @DateStart 	= '2017-03-10 08:00:00'
SET @DateEnd 	= '2017-03-10 09:00:00'

-- SQL Query returns difference in seconds : 3600 seconds
SELECT DATEDIFF(SECOND, @DateStart, @DateEnd) AS [Durée en Seconds]

-- SQL Query returns difference in minutes : 60 minutes
SELECT DATEDIFF(MINUTE, @DateStart, @DateEnd) AS [Durée en Minutes]

-- SQL Query returns difference in hours : 1 hour
SELECT DATEDIFF(HOUR, @DateStart, @DateEnd) AS [Durée en Hours]

How to calculate time difference in hours minutes and seconds with SQL Server?

Below are two different ways to calculate the time difference between two dates.

1. The first solution to get the difference between two timestamps in one single query

To get the difference in hours, minutes and seconds, use this query, it works only if hours are smaller than 99.
Change the total in seconds in Hours, Minutes and Seconds, for example with 3 hours = 3600 * 3 = 14400.

DECLARE @TimeInSeconds INT
SET 	@TimeInSeconds = 14400

SELECT
RIGHT('0' + CAST(@TimeInSeconds / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeInSeconds / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(@TimeInSeconds % 60 AS VARCHAR),2)

2. The second solution to calculate the difference between two dates in one query

These SQL queries computes the time difference using first the seconds (multiplied by one hour in seconds is 60 * 60 = 3600. Then using the milliseconds (multiplied by one hour in milliseconds as 60 * 60 * 1000 = 3600 * 1000 ).

It finally displays the result without the milliseconds using the CONVERT() function and the data date format 108.

select CONVERT(varchar, dateadd(s, 3 * 3600 , getdate()), 108)			AS [Hours Minutes Seconds]
select CONVERT(varchar, dateadd(ms, 3 * 3600 * 1000, getdate()), 108)	AS [Hours Minutes Seconds]

In this short post we have seen how to calculate a SQL Server date difference in hours minutes and seconds.

Here is the official Microsoft SQL Server documentation about the DATEADD() time function.

Was it helpful for you? If so please use it and share it!

Writing about dates, here is how to display the month from an SQL Server date.

Be the first to comment

Leave a Reply