SQL SUBDATE()

The SUBDATE() function returns a date or datetime value after subtracting a specified time interval from a starting date. It is commonly used in queries that need to calculate past dates, such as filtering records from a certain number of days, months, or years before a given date.

Syntax

SUBDATE(date, INTERVAL value unit)

Or, alternatively:

SUBDATE(date, days)

Parameters:

• date: The starting DATE or DATETIME value.
• value: The amount of time to subtract.
• unit: The unit of time to subtract, such as DAY, MONTH, YEAR, or HOUR.
• days: An integer value representing the number of days to subtract.

Return value:

• Returns a DATE when the input is a date.
• Returns a DATETIME when the input includes a time component.

Example 1: Subtracting a Month from a Date

In this example, SUBDATE() subtracts one month from a given date and returns the resulting date:

SELECT SUBDATE('2026-01-21', INTERVAL 1 MONTH);

Copy to clipboard
Copy to clipboard

The output of this code is:

2025-12-21

Copy to clipboard
Copy to clipboard

Example 2: Shorthand Syntax (Days Only)

In this example, SUBDATE() subtracts a specified number of days from a date using the shorthand numeric syntax:

SELECT SUBDATE('2026-01-21', 10);

Copy to clipboard
Copy to clipboard

The output of this code is:

2026-01-11

Copy to clipboard
Copy to clipboard

Example 3: Using Different Time Units

In this example, SUBDATE() subtracts different time units such as years and hours from date and datetime values, including the current date and time:

SELECT SUBDATE('2026-05-15', INTERVAL 1 YEAR);
SELECT SUBDATE('2026-01-21 12:00:00', INTERVAL 3 HOUR);
SELECT SUBDATE(NOW(), INTERVAL 1 HOUR);

Copy to clipboard
Copy to clipboard

The output of this code is:

2025-05-15
2026-01-21 09:00:00
2026-01-22 11:05:30

Copy to clipboard
Copy to clipboard