DATEDIFF

MySQL

DATEDIFF(expr1, expr2)

• expr1: The initial date or datetime value from which the difference is calculated. It can be input as a date string or an existing date/datetime column in a table.
• expr2: The subsequent date or datetime value to which the difference is calculated. Like expr1, this can also be input as a date string or an existing date/datetime column in a table.

Example

SELECT DATEDIFF('2020-07-20','2020-07-01') AS Days;

Output

19

Explanation

The DATEDIFF() function in MySQL calculates the difference in days between two date values. In this example, the function calculates the difference between ‘2020-07-20’ and ‘2020-07-01’ which results in 19 days.

SQL Server

DATEDIFF(interval, date1, date2)

• interval: This parameter represents the part of the date to calculate the difference. It can be year, quarter, month, day, week, hour, minute, second, millisecond, microsecond, nanosecond.
• date1: The start date from which to calculate the difference. It is expressed in a Date format.
• date2: The end date up to which the difference is calculated. It is also expressed in a Date format.

Example

DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
SET @StartDate = '2018-04-01';
SET @EndDate = '2018-05-15';
SELECT DATEDIFF(day, @StartDate, @EndDate);

Output

44

Explanation

The DATEDIFF function in SQL Server calculates the difference between two dates and returns the difference in the unit specified. In this example, the code is calculating the number of days between April 1, 2018 and May 15, 2018. The output of the DATEDIFF function is 44, which means there are 44 days between these two dates.

Oracle

DATEDIFF(end_date, start_date)

• end_date: This is the second input parameter for the DATEDIFF function in Oracle. It signifies the final date and must be provided in a date format that Oracle DataBase can interpret.
• start_date: This is the first input parameter for the DATEDIFF function in Oracle. It represents the initial date and must also be provided in a format that can be interpreted by the Oracle DataBase.

Example

SELECT DATEDIFF(DAY, TO_DATE('2021-01-01', 'YYYY-MM-DD'), TO_DATE('2021-12-31', 'YYYY-MM-DD')) AS difference
FROM dual;

Output

365

Explanation

The DATEDIFF function in Oracle calculates the difference in days between two dates. The dates are ‘2021-01-01’ and ‘2021-12-31’, and the result ‘365’ indicates the total number of days between these two dates.