MySQL DATE_SUB(): Subtract Days from Date or DateTime

MySQL DATE_SUB(): Subtract Days from Date or DateTime

MySQL DATE_SUB() function; In this tutorial, we would love to share with you how to subtract days from date or datetime using MySQL date_sub() function with the help of examples.

MySQL DATE_SUB() Function

In MySQL,  the DATE_SUB () function is used to subtract(decrease) a specified time from a given date. 

Example:- You want to subtract(decrease) 15 days from a given date. You can also to subtract day, week, month, quarter, year from a given date. You can also subtract(decrease) a time value, such as seconds, microseconds, etc.

Syntax

The basic syntax of MySQL DATE_SUB() function is:

DATE_SUB(date,INTERVAL expr unit)

Example-1

Here, we will take the first simple example for demonstration of this function is:

SELECT DATE_SUB('2019-07-23', INTERVAL 6 DAY) AS Result;

Output-1

+------------+
| Result     |
+------------+
| 2019-07-17 |
+------------+

In the above example, we have subtracted the 6 days from the given date.

Example-2

We take the next example of this function with curdate() function of MySQL. In this example, we will subtract the days, weeks, months, years’ in the current date.

 SELECT 
 CURDATE() AS 'Start Date',
 DATE_SUB(CURDATE(), INTERVAL 2 WEEK) AS '-2 Weeks',
 DATE_SUB(CURDATE(), INTERVAL 2 MONTH) AS '-2 Months',
 DATE_SUB(CURDATE(), INTERVAL 2 QUARTER) AS '-2 Quarters',
 DATE_SUB(CURDATE(), INTERVAL 2 YEAR) AS '-2 Years'; 

Output-2

 +------------+------------+------------+-------------+------------+
 | Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |
 +------------+------------+------------+-------------+------------+
 | 2019-07-23 | 2019-07-09 | 2019-05-23 | 2019-01-23  |2017-07-23  |
 +------------+------------+------------+-------------+------------+

Example-3

Next, we take an example of this function with MySQL NOW() function. We will subtract the time units from the given date/time value and also subtract the time unit from the now() function. See the example below for a demonstration:

SELECT DATE_SUB('2019-07-21 10:00:00', INTERVAL 4 HOUR) AS Result,
       DATE_SUB(NOW(), INTERVAL 5 HOUR) AS NOW;

Output-3

 +---------------------+---------------------+
 | Result              | NOW                 |
 +---------------------+---------------------+
 | 2019-07-23 06:00:00 | 2019-07-22 22:04:40 |
 +---------------------+---------------------+

Expected Values

The following table shows the valid unit values and their expected format.

unit ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
SECOND_MICROSECOND‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND‘MINUTES:SECONDS’
HOUR_MICROSECOND‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE‘HOURS:MINUTES’
DAY_MICROSECOND‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE‘DAYS HOURS:MINUTES’
DAY_HOUR‘DAYS HOURS’
YEAR_MONTH‘YEARS-MONTHS’

Conclusion

Here, you have learned how to use MySQL date_sub() function with various examples.

Recommended Mysql Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *