DATE arithmetic (DATEDIFF, DATE_ADD) in SQL

Introduction

We use date arithmetic to find the difference between dates or to add days to a date. This helps us answer questions about time easily.

To find how many days passed between two events, like order date and delivery date.

To calculate a future date, like a due date 10 days after today.

To check if a subscription expired by comparing current date and expiry date.

To find the age of something by subtracting birth date from today.

To schedule reminders by adding days to a start date.

Syntax

SQL

DATEDIFF(date1, date2)
DATE_ADD(date, INTERVAL number DAY)

DATEDIFF returns the number of days between date1 and date2 (date1 - date2).

DATE_ADD adds a specified number of days to a date.

Examples

Finds how many days between June 10, 2024 and June 1, 2024.

SQL

SELECT DATEDIFF('2024-06-10', '2024-06-01');

Adds 5 days to June 1, 2024, resulting in June 6, 2024.

SQL

SELECT DATE_ADD('2024-06-01', INTERVAL 5 DAY);

Finds how many days have passed since January 1, 2024 until today.

SQL

SELECT DATEDIFF(CURRENT_DATE, '2024-01-01');

Finds the date 30 days from today.

SQL

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY);

Sample Program

This query calculates the days between June 15 and June 10, 2024, and adds 7 days to June 10, 2024.

SQL

SELECT
  DATEDIFF('2024-06-15', '2024-06-10') AS days_difference,
  DATE_ADD('2024-06-10', INTERVAL 7 DAY) AS new_date;

Important Notes

DATEDIFF counts full days between dates, ignoring time parts.

DATE_ADD can add other intervals like MONTH or YEAR, but here we focus on DAY.

Always use quotes around date strings in SQL.

Summary

DATEDIFF helps find the number of days between two dates.

DATE_ADD helps find a new date by adding days to an existing date.

These functions make working with dates easy and clear.