Calculate the difference between two dates in hours, minutes, and seconds with SQL Server functions.
With the SQL Server time functions, it is possible to calculate the difference between two dates in hours, minutes, and seconds. But this calculation is done only in one unit at a time. For example, only seconds, or only minutes or only hours.
This SQL Server scripts uses the DATEDIFF function and calculate difference in hours, minutes, and seconds. So, calculating the time between a start date and end date is not straightforward with SQL Server. This post shows you how to do it with simple queries, explained step by step.
Table of Contents
1. Time difference between two timestamps in T-SQL
First, as an example, these 3 simple SQL Server queries uses the DATEDIFF functions. And the two timestamps stored in variables have exactly one hour of difference.
However, each query only returns one unit, which is not enough for our purpose. To get the complete result in hours, minutes, and seconds, we need to concatenate multiple queries together.
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.
- First, calculate the difference in seconds
- Then in minutes
- Last one in hours
-- Declare and initialize two variables with exactly one hour of difference: 9 - 8 = 1h DECLARE @DateStart DATETIME DECLARE @DateEnd DATETIME SET @DateStart = '2022-03-10 08:00:00' SET @DateEnd = '2022-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]
Or in a more concise format, using only 3 lines of code, but calculating only the difference in hours:
-- Declare and initialize two variables with exactly one hour of difference: 9 - 8 = 1h DECLARE @DateStart DATETIME = '2022-03-10 08:00:00', @DateEnd DATETIME = '2022-03-10 09:00:00'; -- SQL Query returns difference in hours : 1 hour SELECT DATEDIFF(HOUR, @DateStart, @DateEnd) AS [Difference in Hours];
Find below a screenshot after the execution of the query using SSMS.

2. Difference between two SQL Server dates with CAST function
In this second method, to get the time difference in hours, minutes, and seconds, use this query with CAST and the Modulo operator. However, 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 time difference using the SQL DATEADD function
In this third approach, these 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). And 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. Calculate the difference between two dates using the DATEPART function
Another option to calculate the difference in minutes and seconds is to use the T-SQL DATEPART function.
DECLARE @DateStart DATETIME = '2022-03-10 08:00:00', @DateEnd DATETIME = '2022-03-10 09:00:00'; SELECT DATEPART(HOUR, @DateEnd) - DATEPART(HOUR, @DateStart) as [Difference in Hours], DATEPART(MINUTE, @DateEnd) - DATEPART(MINUTE, @DateStart) as [Difference in Minutes], DATEPART(SECOND, @DateEnd) - DATEPART(SECOND, @DateStart) as [Difference in Seconds];
Many ways to calculate the time difference in SQL Server
In this tutorial, we discussed how to calculate the difference between two SQL Server dates using T-SQL code. By using built-in date functions, you can easily determine the difference in hours, minutes, and seconds.
To optimize your solution, we suggest combining multiple functions. For more information on the SQL Server DATEADD() time function, you can refer to the official Microsoft documentation.
Questions about SQL Server time functions
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]”
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]”
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]”
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]”
Useful T-SQL queries and tips that might interest you
- Query to display the month name in SQL Server
- Last time table was updated in SQL Server
- Avoid the SQL Server Arithmetic overflow error converting expression
- Check if table exists in SQL Server
SQL Server string longer than 8000 characters