How to Calculate the Difference Between Two Dates in T-SQL
Database:
Operators:
Table of Contents
Problem
You have two columns of the date type 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 arrival
and departure
, or the number of days from arrival
to departure inclusively.
The travel
table looks like this:
id | departure | arrival |
---|---|---|
1 | 2018-03-25 | 2018-04-05 |
2 | 2019-09-12 | 2019-09-23 |
3 | 2018-07-14 | 2018-07-14 |
4 | 2018-01-05 | 2018-01-08 |
Solution
SELECT id, departure, arrival, DATEDIFF( day , departure, arrival) AS date_difference, DATEDIFF( day , departure, arrival) + 1 AS days_inclusive FROM travel; |
The result is:
id | departure | arrival | date_difference | days |
---|---|---|---|---|
1 | 2018-03-25 | 2018-04-05 | 11 | 12 |
2 | 2019-09-12 | 2019-09-23 | 11 | 12 |
3 | 2018-07-14 | 2018-07-14 | 0 | 1 |
4 | 2018-01-05 | 2018-01-08 | 3 | 4 |
Discussion
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate)
function. The datepart
argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year
, quarter
, month
, day
, minute
, etc. In this example, you want to express the difference in days, so the first argument is day. The two other arguments are the date from which you start counting and the date when you stop counting – In this case, departure
and arrival
, respectively.
In most cases, what you really want is the number of days from the first date to the second date inclusively. Then you need to add 1 day to the difference in days: DATEDIFF(day, departure, arrival) + 1
.