Computers & ProgrammingBackend DevelopmentSQL

MySQL DATE_SUB Function

The MySQL DATE_SUB function subtracts a custom time interval from a date.

Employees Table

employeeID employeeName retirementDate
1000 John Smith 2025-12-03
1001 Fred White 2031-10-12
1002 Jane Scott 2028-05-01
1003 Samuel Williams 2021-01-03

In this example, we want to determine the year before an employee is scheduled to retire.

Syntax

DATE_SUB(date,INTERVAL interval format)

Formats

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Example

SELECT employeeName as [Employee Name], DATE_SUB(retirementDate INTERVAL 1 YEAR) as [Eligible for Early Retirement]
FROM employees

Results

Employee Name Eligible for Early Retirement
John Smith 2024
Fred White 2030
Jane Scott 2027
Samuel Williams 2020

Leave a Comment

Your email address will not be published.

Scroll to Top