DATE_SUB Function - SQL
Overview
The DATE_SUB function in SQL subtracts a specified time interval from a date. It is useful for date arithmetic, such as calculating past dates based on a given date.
Example:
SELECT
DATE_SUB('2000-01-01', INTERVAL 10 DAY)
Syntax:
SELECT
DATE_SUB(date, INTERVAL value unit)
date is the starting date from which you want to subtract the interval.
value is the numeric value of the interval you want to subtract.
unit is the unit of the interval (e.g., DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).
Sample Data:
| first_name | birthday |
|---|---|
| Frank | 1994-03-15 00:00:00 |
| Jane | 1980-06-14 00:00:00 |
| Ashley | 1975-12-05 00:00:00 |
| Glenn | 1988-03-23 00:00:00 |
| Kelly | 1979-10-11 00:00:00 |
| Richard | 1985-08-30 00:00:00 |
| George | 1992-02-18 00:00:00 |
| Kyle | 1983-04-07 00:00:00 |
| James | 1978-09-27 00:00:00 |
| Gustavo | 1987-11-21 00:00:00 |
unit: All available inputs
YEAR: Represents the year component of a date.
MONTH: Represents the month component of a date.
DAY: Represents the day component of a date.
HOUR: Represents the hour component of a time.
MINUTE: Represents the minute component of a time.
SECOND: Represents the second component of a time.
MICROSECOND: Represents the microsecond component of a time.
QUARTER: Represents the quarter component of a date.
WEEK: Represents the week component of a date.
DAY_MICROSECOND: Represents a combination of days and microseconds.
DAY_SECOND: Represents a combination of days and seconds.
DAY_MINUTE: Represents a combination of days and minutes.
DAY_HOUR: Represents a combination of days and hours.
YEAR_MONTH: Represents a combination of years and months.
HOUR_MICROSECOND: Represents a combination of hours and microseconds.
HOUR_SECOND: Represents a combination of hours and seconds.
HOUR_MINUTE: Represents a combination of hours and minutes.
MINUTE_MICROSECOND: Represents a combination of minutes and microseconds.
MINUTE_SECOND: Represents a combination of minutes and seconds.
SECOND_MICROSECOND: Represents a combination of seconds and microseconds.
Example: Hard-coded value
In this example, we are subtracting 10 days to the ‘2000-01-01’ date for a final output of ‘1999-12-22.’
Example: Query without the WHERE Statement
In this example, we are subtracting 10 days from the birthday column in the company.employees table. You could see the side by side differences in the output.
Example: Query with the WHERE Statement
In this example, we are subtracting 10 days from the birthday column in the company.employees table where the first_name column equals ‘Kelly.’ You could see the side by side differences in the output.
