# How to Calculate the Difference Between Two Dates in SQLite

• JULIANDAY

## Problem:

You have two columns of the date type in SQLite database 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` and the number of days from arrival to departure inclusively.

The `travel` table looks like this:

iddeparturearrival
12018-03-252018-04-05
22019-09-122019-09-23
32018-07-142018-07-14
42018-01-052018-01-08

## Solution:

```SELECT
id,
departure,
arrival,
JULIANDAY(arrival) - JULIANDAY(departure) AS date_difference,
JULIANDAY(arrival) - JULIANDAY(departure) + 1 AS days_inclusive
FROM travel;
```

The result is:

iddeparturearrivaldate_differencedays
12018-03-252018-04-051112
22019-09-122019-09-231112
32018-07-142018-07-1401
42018-01-052018-01-0834

## Discussion:

To count the difference between dates in SQLite database, use the `JULIANDAY()` function, which counts the number of days since noon in Greenwich on November 24, 4714 B.C. You use the `JULIANDAY(date)` for each of the two dates; simply subtract them using a minus sign (`-`):

`JULIANDAY(arrival) - JULIANDAY(departure)`

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:

`JULIANDAY(arrival) - JULIANDAY(departure) + 1.`