MySQL - DATEDIFF() Function

The MySQL DATEDIFF() function is used to calculate the difference in days between two dates. It takes two date expressions as arguments and returns the number of days between them.

A NULL value is returned by this function if either of the two arguments is NULL.

Syntax

Following is the syntax of MySQL DATEDIFF() function −

DATEDIFF(end_date, start_date);

Parameters

This method accepts two parameters. The same is described below −

  • end_date: The date to which we want to calculate the difference.

  • start_date: The date from which we want to calculate the difference.

Return value

This function returns an integer representing the difference in days between the end_date and start_date.

Example

In the following example, we are using the MySQL DATEDIFF() function to calculate the difference between the specified dates −

SELECT DATEDIFF('2023-09-05', '2023-09-05') As Result;

Output

This will produce the following result −

Result
365

Example

The below query calculates the difference in days between the current date and the specified date −

SELECT DATEDIFF(CURDATE(), '2023-10-22') As Result;

Output

Following is the output −

Result
31

Example

In the following example, let us create a table named ORDERS using CREATE TABLE statement −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Here, we use DATEDIFF() function to calculate the difference in days between the current date and the datetime values in the DATE column −

SELECT OID, DATE, DATEDIFF(CURDATE(), DATE) as Result 
FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 123778
100 2009-10-08 00:00:00 123778
101 2009-11-20 00:00:00 122746
103 2008-05-20 00:00:00 135922
Advertisements