# How to Find the Difference Between Two Datetimes in T-SQL

• 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
```