Calculate the difference between two dates with SQL Server

Calculate the difference between two dates in hours, minutes, and seconds with SQL Server functions.

With the SQL Server time function DATEDIFF, it is possible to calculate the difference between two dates in hours, minutes, and seconds but only in one unit at a time. For example, only seconds, or only minutes or only hours. This SQL Server script uses the DATEDIFF function and calculate in hours, minutes, and seconds. 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.

T-SQL queries to calculate difference between dates

Therefore, as an example, this simple SQL query is using the SQL Server DATEDIFF function with two timestamps having exactly one hour of difference. And every query gives only one unit. In other words, 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.

1. Calculate SQL Server time difference in hours minutes and seconds

The queries below show 3 different types of results. However, every time unit is not a time but only one single unit. So, it is mandatory to concatenate them and convert them.

  1. First, calculate the difference in seconds
  2. Then minutes
  3. Last the hours
-- 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 [Difference in Seconds]

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

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

2. Difference between two timestamps in one query

For instance, to get the difference in hours, minutes, and seconds, use this query, it works only if hours are smaller than 99. Moreover, 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)

3. Calculate the difference using CONVERT and DATEADD functions

Indeed, 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). Certainly, it finally displays the result without the milliseconds using the CONVERT function and the data date format 108.

-- Using the seconds as a base for conversion
select CONVERT(varchar, dateadd(s, 3 * 3600 , getdate()), 108) AS [Hours Minutes Seconds]

-- Using the milliseconds as a base for conversion
select CONVERT(varchar, dateadd(ms, 3 * 3600 * 1000, getdate()), 108) AS [Hours Minutes Seconds]

4. Questions about SQL Server time functions

How to calculate a time difference in hours with a SQL Server query?

To calculate a time difference between datetime in hours, take the two date and times and do the difference by using the HOUR option combined with the DATEDIFF time function in SQL Server :
“SELECT DATEDIFF(HOUR, @DateStart, @DateEnd) AS [Difference in Hours]”

How to calculate a SQL time difference in minutes?

To calculate a time difference between two dates in minutes, use the two date and times and calculate the difference with the MINUTE option of the T-SQL DATEDIFF function :
“SELECT DATEDIFF(MINUTE, @DateStart, @DateEnd) AS [Difference in Minutes]”

How to display a time difference in seconds with SQL Server?

In order to calculate the exact time difference between two timestamps to the seconds, do the difference using the SECOND option provided by the SQL Server database :
“SELECT DATEDIFF(SECOND, @DateStart, @DateEnd) AS [Difference in Seconds]”

How to calculate a time difference in milliseconds with MS SQL?

To calculate precisely the time difference in milliseconds, use the T-SQL built-in time function and the MILLISECOND keyword :
“SELECT DATEDIFF(MILLISECOND, @DateStart, @DateEnd) AS [Difference in Milliseconds]”

Conclusion on time difference calculation in T-SQL

In this short post we have seen how to calculate the SQL Server datetime difference in hours, minutes, and seconds using the T-SQL DATEDIFF function. Amongst many possible solutions, it is recommended to use multiple functions together. Here is the official Microsoft documentation about the SQL Server DATEADD() time function.

To go further with T-SQL useful queries and tips

Be the first to comment

Leave a Reply

Your email address will not be published.


*