Back to list T-SQL How to Calculate the Difference Between Two Datetimes in T-SQL Database: T-SQL Operators:DATEDIFF(), WITH, %, FLOOR(), CONCAT() Problem: You have two columns of the type datetime and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1 (difference in seconds): SELECT id, departure, arrival, DATEDIFF(second, departure, arrival) AS difference FROM travel; The result is: iddeparturearrivaldifference 12018-03-25 12:00:002018-04-05 07:30:00934200 22019-09-12 15:50:002019-10-23 10:30:303523230 32018-07-14 16:15:002018-07-14 20:40:3015930 42018-01-05 08:35:002019-01-08 14:00:0031814700 Discussion: To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond, second, minute, hour, day, week, month, quarter, or year. Here, you'd like to get the difference in seconds, so choose second. To get the difference in hours, choose hour; for the difference in months, choose month, etc. The startdate and the enddate arguments are the starting and the ending datetime columns, respectively (here, departure and arrival, respectively). Solution 2 (difference in days, hours, minutes, and seconds): WITH difference_in_seconds AS ( SELECT id, departure, arrival, DATEDIFF(SECOND, departure, arrival) AS seconds FROM travel ), differences AS ( SELECT id, departure, arrival, seconds, seconds % 60 AS seconds_part, seconds % 3600 AS minutes_part, seconds % (3600 * 24) AS hours_part FROM difference_in_seconds ) SELECT id, departure, arrival, CONCAT( FLOOR(seconds / 3600 / 24), ' days ', FLOOR(hours_part / 3600), ' hours ', FLOOR(minutes_part / 60), ' minutes ', seconds_part, ' seconds' ) AS difference FROM differences; The result is: iddeparturearrivaldifference 12018-03-25 12:00:002018-04-05 07:30:0010 days 19 hours 30 minutes 0 seconds 22019-09-12 15:50:002019-10-23 10:30:3040 days 18 hours 40 minutes 30 seconds 32018-07-14 16:15:002018-07-14 20:40:300 days 4 hours 25 minutes 30 seconds 42018-01-05 08:35:002019-01-08 14:00:00368 days 5 hours 25 minutes 0 seconds Discussion: First, calculate the difference between the arrival and the departure in seconds, using the DATEDIFF() function (the first CTE, named difference_in_seconds), just as in Solution 1. Then, calculate how many seconds there are in excess of whole minutes (seconds_part) to be used later to calculate the seconds, how many seconds there are in excess of whole hours (minutes_part) to be used later to calculate the minutes, and how many seconds there are in excess of whole hours (hours_part) to be used later to calculate the hours. To do this, use the % operator. For example, an hour has 3600 seconds, so to find how many seconds there are in minutes_part, find the remainder from the division by 3600 like this: seconds % 3600 AS minutes_part Similarly, there are 3600 * 24 seconds in a day, so to calculate how many seconds there are in hours_part, write: seconds % (3600 * 24) AS hours_part Once these remainders are calculated (in the second CTE, named differences), you can finally get the difference in days, hours, minutes, and seconds. To get the number of seconds, minutes, hours, and days, divide the number of seconds in the remainder by the corresponding number of seconds in days, hours, or minutes. For example, to find out how many minutes should be displayed, take minutes_part and divide it by 60, since there are 60 minutes in an hour. You only need the integer part from this (i.e., without the decimal part), so use the FLOOR() function like this: FLOOR(minutes_part / 60) Finally, you simply need to display in one string what you've calculated. To do this, use the CONCAT() function in the outer query: CONCAT( FLOOR(seconds / 3600 / 24), ' days ', FLOOR(hours_part / 3600), ' hours ', FLOOR(minutes_part / 60), ' minutes ', seconds_part, ' seconds' ) AS difference The solution presented here returns a datetime difference as a text. You can easily modify the solution to get only the numbers without any text. You can also store days, hours, minutes, and seconds in different columns: FLOOR(seconds / 3600 / 24) AS days, FLOOR(hours_part / 3600) AS hours, FLOOR(minutes_part / 60) AS minutes, seconds_part AS seconds Recommended courses: SQL Basics in SQL Server Common Functions in SQL Server Data Types in SQL Recommended articles: How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic Performing Calculations on Date- and Time-Related Values How to Get the First Day of the Week in SQL Server See also: How to Add Days to a Date in T-SQL How to Subtract 30 Days from a Date in T-SQL How to Get Yesterday’s Date in T-SQL How to Format a Date in T-SQL How to Calculate the Difference Between Two Dates in T-SQL Tags: T-SQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.