How to calculate a Sql Server time difference hours minutes and seconds with datediff ? The SQL Server function DATEDIFF() allows to calculate the difference between two timestamps, but only in one unit. 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.
How to calculate SQL Server time difference in hours, minutes and seconds ?
Therefore, as an example, this simple SQL query is using the 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.
SQL Server time difference with datediff in hours minutes and seconds
The queries below show:
- First, SQL Datediff in hours
- Then the SQL Datediff in minutes
- Last the SQL Datediff in seconds
However every time unit is not a time but only one single 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 [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]
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
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)
2. The second solution is to calculate the time difference in hours minutes and seconds between two dates in one SQL Server query
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]
In this short post we have seen how to calculate a SQL Server datetime difference in hours, minutes, and seconds using the datediff function.
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!
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]”
Likewise, to go further with T-SQL queries. Here is how to manage SQL Server text with more than 8000 characters within stored procedures.