MySQL – Get Difference Between Two Dates in Days

In SQL language, DATEDIFF() function is used to find the interval between 2 specified dates. The function takes 2 dates as parameters and returns the number of days between the 2 dates.
 

Syntax:

In MySQL, DATEDIFF() function is used in a query with the following format:

SELECT DATEDIFF(date1, date2);

The dates must be in DATE format (cf. YYYY-MM-DD) or DATETIME (cf. YYYY-MM-DD HH:MM:SS). For a positive result, date1 must be more recent than date2.
 

 

Example :

The function can be used to compare 2 dates in a table or can be used by using the dates directly.

Here is a list of practical examples:

SELECT DATEDIFF('2020-01-08', '2020-01-01'); -- output: 7
SELECT DATEDIFF('2020-01-08 00:00:00', '2020-01-01 00:00:00'); --output:7
SELECT DATEDIFF('2020-01-01', '2020-01-09'); -- output: -7
SELECT DATEDIFF('2021-01-01', '2020-01-01'); -- output: 365
SELECT DATEDIFF('2020-01-02', '2020-01-01'); -- output: 1
SELECT DATEDIFF('2020-01-02 23:59:59', '2020-01-01'); -- output: 1

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More