How to use the MySQL SUBDATE() function

In this article, we will learn how to use the MySQL SUBDATE() function, which subtracts a specified interval from a date or datetime value.

Posted on

In this article, we will learn how to use the MySQL SUBDATE() function, which subtracts a specified interval from a date or datetime value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates and intervals.

Syntax

The syntax of the SUBDATE() function is as follows:

SUBDATE(date, interval)

The date parameter can be any valid date or datetime expression, or a string that can be converted to a date or datetime value. The interval parameter can be either a numeric expression that represents the number of days to subtract, or a string expression that specifies the type and value of the interval to subtract. The interval parameter can use any of the date and time units that are supported by MySQL. If either parameter is NULL, the function returns NULL. The SUBDATE() function returns a date or datetime value that is the result of subtracting the interval from the date. For example, SUBDATE('2023-01-15', 1) returns ‘2023-01-14’, which is one day before the given date.

Examples

Let’s see some examples of how to use the SUBDATE() function in MySQL.

Example 1: Subtract a number of days from a date value

We can use the SUBDATE() function to subtract a number of days from a date value. For example:

SELECT SUBDATE('2023-01-15', 1) AS result;

This query will subtract one day from the date value ‘2023-01-15’. The query will return ‘2023-01-14’, which is the previous date.

Example 2: Subtract a number of days from a datetime value

We can use the SUBDATE() function to subtract a number of days from a datetime value. For example:

SELECT SUBDATE('2023-01-15 10:02:34', 1) AS result;

This query will subtract one day from the datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-14 10:02:34’, which is the same time on the previous date.

Example 3: Subtract a different type of interval from a date value

We can use the SUBDATE() function to subtract a different type of interval from a date value, such as a month, a year, or a week. For example:

SELECT SUBDATE('2023-01-15', INTERVAL 1 MONTH) AS result;

This query will subtract one month from the date value ‘2023-01-15’. The query will return ‘2022-12-15’, which is the same day on the previous month.

Example 4: Subtract a different type of interval from a datetime value

We can use the SUBDATE() function to subtract a different type of interval from a datetime value, such as an hour, a minute, or a second. For example:

SELECT SUBDATE('2023-01-15 10:02:34', INTERVAL 1 HOUR) AS result;

This query will subtract one hour from the datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 09:02:34’, which is one hour earlier.

Example 5: Subtract a fractional interval from a date or datetime value

We can use the SUBDATE() function to subtract a fractional interval from a date or datetime value, such as a half day, a quarter hour, or a tenth of a second. For example:

SELECT SUBDATE('2023-01-15 10:02:34', INTERVAL 0.5 DAY) AS result;

This query will subtract half a day from the date or datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 00:02:34’, which is 12 hours earlier.

There are some other functions that are related to the SUBDATE() function, and can be useful for working with dates and intervals. Here are some of them:

  • ADDDATE(): This function adds a specified interval to a date or datetime value. For example, ADDDATE('2023-01-15', 1) returns ‘2023-01-16’.
  • DATE_SUB(): This function is a synonym for the SUBDATE() function. For example, DATE_SUB('2023-01-15', 1) returns ‘2023-01-14’.
  • DATE_ADD(): This function is a synonym for the ADDDATE() function. For example, DATE_ADD('2023-01-15', 1) returns ‘2023-01-16’.
  • DATE(): This function returns the date part of a date or datetime value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
  • TIME(): This function returns the time part of a date or datetime value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.

Conclusion

In this article, we learned how to use the MySQL SUBDATE() function, which subtracts a specified interval from a date or datetime value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates and intervals.