Scaler
Academy
Data Science
Neovarsity
New
Skill Test
Free Masterclass
Search for Articles, Topics
MySQL Date_Sub

MySQL Date_Sub

By Shivam Singla
4 mins read
Last updated: 4 May 2023
212 views
Topics Covered

Overview

In MySQL, DATE_SUB() is a built-in function used to subtract a specified time interval from a given date or datetime value. The DATE_SUB() function can be useful in many situations, such as when you need to calculate a deadline or expiration date based on a starting date and a duration, or when you need to perform date arithmetic in a SQL query.

Syntax of MySQL DATE_SUB() Function

The DATE_SUB function in MySQL has the following syntax:

Parameters of MySQL DATE_SUB() Function

  • Date :
    The date from which the interval value must be deducted.
  • Value :
    Here, the value to subtract is a number that is a period of time. Numbers can be both positive and negative.
  • Unit
    The interval's unit type, such as day, month, minute, hour, etc.

Return Value of MySQL DATE_SUB() Function

After subtracting the supplied time or date, it returns the new date.

How does the MySQL DATE_SUB() Function Work?

The DATE_SUB() function returns the result after deducting a date or time period from the supplied date or DateTime value.

  1. It returns a DATE value if date is a DATE value and the unit is YEAR, MONTH, or DAY.
  2. DATETIME is returned if date is a DATE value and the unit is HOURS, MINUTES, or SECONDS.
  3. It returns a DATETIME value if the input value is DATETIME.
  4. DATETIME is returned if date is a TIME value and the unit is YEAR, MONTH, or DAY.
  5. When the date is a TIME value and just the HOURS, MINUTES, and SECONDS components are used in the calculation, a TIME value is returned. (MySQL version 8.0.28 and later)
  6. Otherwise, a NULL value is returned. The DATE_SUB() function returns NULL if the first input is an invalid date, a corrupted date, or NULL.

Examples

1) Subtraction of 5 Years

SQL Query:

Output:

2) Subtraction of -2 Months

SQL Query:

Output:

Explanation: Here as we are subtracting -2 months from the specified date, MySQL will add 2 months in the specified date because -(-2) is +2.

3) Subtraction of 13 Days

SQL Query:

Output:

4) Subtraction of 3 Hours

SQL Query:

Output:

5) Subtraction of 5 Minutes

SQL Query:

Output:

6) Subtraction of 2 Seconds

SQL Query:

Output:

7) Subtraction of 2 Quarters

SQL Query:

Output:

8) Subtraction of 9 Weeks

SQL Query:

Output:

9) Subtraction of 13 Days and Some Time

SQL Query:

Output:

10) Subtraction of 2 Days and 5 Hours

SQL Query:

Output:

11) Invalid Date Input

SQL Query:

Output:

Explanation: Here as we are subtracting one day from the specified date but the specified date is invalid as in February month of the year 2021 there were only 28 days so the result will return NULL due to an invalid date as input.

Conclusion

Important information for the MySQL DATE_SUB() function:

  • In MySQL, the DATE_SUB() method is used to subtract a defined time period from a date value.
  • Several units of time, including YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND, WEEK, and QUARTER, can be used to specify the time interval.
  • Three parameters are required for the DATE_SUB function: the date or datetime value that needs to be subtracted from; an interval value that indicates the amount of time to be removed; and the unit of the interval value.
  • A date or datetime value is returned by MySQL's DATE_SUB function. The data type of the parameter provided to the function determines the return type.

See Also

Related MYSQL functions:

  • DATE()
  • LAST_DAY()
  • DATE_FORMAT()
  • DATE_ADD()
  • DATEDIFF()