MySQL - SUBDATE() Function

The MYSQL SUBDATE() function is used to subtract the specified interval to a date value. This function accepts two arguments: date and a specified interval that needs to be subtracted from the date value. The return value of this function is the final date after the interval is subtracted. A NULL value is returned if both date and interval arguments are NULL.

Syntax

Following is the syntax of MySQL SUBDATE() function −

SUBDATE(date, INTERVAL expr unit);

Parameters

This method accepts three parameters. The same is described below −

  • date is the value representing the date it can be of the type String, DATE (YEAR, MONTH, and DAY), DATETIME (HOURS, MINUTES or, SECONDS) or, TIMESTAMP.
  • expr is the value representing the interval value.
  • unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

Return value

This function returns a new date that is the result of subtracting the specified time interval from the original date.

Another Syntax

This function has another syntax as shown below. It accepts the expression representing the date and the interval representing the number of days that is to be subtracted from the given date −

SUBDATE(expr, days);

Example

In the following query, we are subtracting 20 days from the date '2023-09-05' using the MySQL SUBDATE function() −

SELECT SUBDATE('2023-09-05', INTERVAL 20 DAY) As Result;

Output

This will produce the following result −

Result
2023-08-16

Example

Following is another example, where we are subtracting a specific duration from the given date and time −

SELECT SUBDATE('2023-05-23 20:40:32.88558', '13 6:4:1.222222')
As Result;

Output

Following is the output −

Result
2018-05-10 20:40:32.885580

Example

Following example demonstrates the second syntax of this function where we pass the number of days to be subtracted as an interval −

SELECT SUBDATE('1995-11-15', 554) As Result;

Output

Following is the output −

Result
1994-05-10

Example

We can also pass negative values as arguments to this function −

SELECT SUBDATE('1995-11-15', -554) As Result;

Output

Following is the output −

Result
1997-05-22

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Here, we are using the SUBDATE() function to subtract 14 years from the values in the "DATE" column −

SELECT OID, DATE, SUBDATE(DATE, INTERVAL 14 YEAR)
As Result From ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 1995-10-08 00:00:00
100 2009-10-08 00:00:00 1995-10-08 00:00:00
101 2009-11-20 00:00:00 1995-11-20 00:00:00
103 2008-05-20 00:00:00 1994-05-20 00:00:00
Advertisements