How to Find Difference Between Dates in SQLite

1. Find the Date Difference by Calculating the Date Only
2. Find the Date Difference by Calculating Both the Time and Date

If you are working on any validation task, you may need to calculate the difference between dates to check validity. We can easily do this by using SQLite.

In this article, we will see how we can find the difference between the two dates and discuss examples and explanations to make it easier.

When we find the difference between the dates, we can follow two criteria here. We can calculate only the dates or the time and date.

For this purpose, we will use an SQLite function, JULIANDAY(), which mainly works with dates and times.

Find the Date Difference by Calculating the Date Only

In our example below, we will calculate the difference between two dates by using the date only. Have a look at the below example query.

SELECT JULIANDAY('2016-04-05') - JULIANDAY('2016-03-25');

You will get the below output when you run the above example query.

11

Find the Date Difference by Calculating Both the Time and Date

In our below example, we will calculate the difference between 2 dates by using both time and date. Have a look at the below example query.

SELECT JULIANDAY('2016-04-05 07:30:00') - JULIANDAY('2016-03-25 12:00:00');

You will get the below output when you run the above example query.

10.8125

Please note that if you are willing to get the exact difference between two dates, you need to calculate it by using both time and date. But if your database doesn’t provide the time, you can use one date to find the difference between the two dates.

Please note that the queries we provided in this article are written for SQLite.