MySQL Fulltext Search
MySQL - Fulltext Search
MySQL - Natural Language Fulltext Search
MySQL - Boolean Fulltext Search
MySQL - Query Expansion Fulltext Search
MySQL - ngram Fulltext Parser

MySQL - DATE_SUB() Function

The MySQL DATE_SUB() function is a synonym to the MySQL SUBDATE() function.

DATE_SUB() function is also used to subtract the specified interval from a date value. This function accepts two arguments: one is a date while the other is the interval that needs to be subtracted from the specified date value. The result obtained will be 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 DATE_SUB() function −

DATE_SUB(date, INTERVAL expr unit);

Parameters

This method accepts 3 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.

The unit can be mixed values as − SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

Return value

This function returns a new date or datetime value after subtracting the specified time interval from the original date.

Example

Following example demonstrates the usage of the DATE_SUB() function −

SELECT DATE_SUB('2015-09-05', INTERVAL 20 DAY);

Output

Following output is obtained −

DATE_SUB('2015-09-05', INTERVAL 20 DAY)
2015-08-16

Example

Following is another example of this function −

SELECT DATE_SUB('2008-01-02', INTERVAL 4 YEAR);

Output

Following output is obtained −

DATE_SUB('2008-01-02', INTERVAL 4 YEAR)
2004-01-02

Example

In the following example we are passing DATETIME value for date −

SELECT DATE_SUB('2018-05-23 20:40:32.88558', INTERVAL 3 WEEK);

Output

Following output is obtained −

DATE_SUB('2018-05-23 20:40:32.88558', INTERVAL 3 WEEK)
2018-05-02 20:40:32.885580

Example

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

SELECT DATE_SUB('2015-09-05', INTERVAL -20 DAY);

Output

Following output is obtained −

DATE_SUB('2015-09-05', INTERVAL -20 DAY)
2015-09-25

Example

Following example demonstrates the usage of various units available in the DATE_SUB() function.

We are using the YEAR_MONTH unit with an INTERVAL expression in the following query −

SELECT DATE_SUB('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH );

Following output is obtained −

DATE_SUB('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH )
2015-09-22

We are using the WEEK unit with an INTERVAL expression in the following query −

SELECT DATE_SUB('2021-03-22', INTERVAL '3' WEEK );

Following output is obtained −

DATE_SUB('2021-03-22', INTERVAL '3' WEEK )
2021-03-01

We are using the QUARTER unit with an INTERVAL expression in the following query −

SELECT DATE_SUB('2021-03-22', INTERVAL '3' QUARTER );

Following output is obtained −

DATE_SUB('2021-03-22', INTERVAL '3' QUARTER )
2020-06-22

We are using the MINUTE_MICROSECOND unit with an INTERVAL expression in the following query −

SELECT DATE_SUB('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND);

Following output is obtained −

DATE_SUB('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND)
2021-03-21 23:49:48.637809

We are using the SECOND_MICROSECOND unit with an INTERVAL expression in the following query −

SELECT DATE_SUB('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND);

Following output is obtained −

DATE_SUB('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND)
2021-03-21 23:59:47.637809

We are using the MINUTE_SECOND unit with an INTERVAL expression in the following query −

SELECT DATE_SUB('2021-03-22', INTERVAL '10.21' MINUTE_SECOND);

Following output is obtained −

DATE_SUB('2021-03-22', INTERVAL '10.21' MINUTE_SECOND)
2021-03-21 23:49:39

Example

Let us create another table with name SALES in MySQL database using CREATE TABLE statement as follows −

CREATE TABLE SALES(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DispatchTime time,
   Price INT,
   Location VARCHAR(255)
);

Now, we will insert 5 records in SALES table using INSERT statements −

INSERT INTO SALES VALUES 
(1, 'Keyboard', 'Rob', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad'),
(2, 'Earphones', 'John', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vizag'),
(3, 'Mouse', 'Jane', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada'),
(4, 'Mobile', 'Sam', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai'),
(5, 'Headset', 'Sara', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query subtracts 25 QUARTER to the entities of the DispatchDate column −

SELECT ProductName, CustomerName, DispatchDate, Price,
DATE_SUB(DispatchDate, INTERVAL -25 QUARTER) AS DATE_SUB 
FROM SALES;

Output

The output is displayed as follows −

ProductName	CustomerName	DispatchDate	Price	DATE_SUB
Keyboard	Rob	2019-09-01	7000	2025-12-01
Earphones	John	2019-05-01	2000	2025-08-01
Mouse	Jane	2019-03-01	3000	2025-06-01
Mobile	Sam	2019-03-01	9000	2025-06-01
Headset	Sara	2019-04-06	6000	2025-07-06

Advertisements