MySQL SUBDATE(): function; In this tutorial, i am going to show you how to subtract days, week, month, year, minutes, hours using MySQL SUBDATE() function.
MySQL SUBDATE() Function
The MySQL SUBTIME() function is used to subtract a time interval from a time or datetime value.
Syntax of MySQL SUBDATE() Function
The basic syntax of MySQL SUBDATE() function is:
SUBDATE(date,INTERVAL expr unit)
Example 1 – MySQL Subtract Days from DateTime
Let’s take an first simple example using MySQL subdate() function; as follows:
SELECT SUBDATE('2019-07-23', INTERVAL 6 DAY) AS Result;
Output-1
+------------+ | Result | +------------+ | 2019-07-17 | +------------+
Example 2 – MySQL Subtract Week, Month, Year from DateTime
Let’s take an example to subtract the days, weeks, months, years’ in the current date using Mysql subdate() function; as follows:
SELECT CURDATE() AS 'Start Date', SUBDATE(CURDATE(), INTERVAL 2 WEEK) AS '-2 Weeks', SUBDATE(CURDATE(), INTERVAL 2 MONTH) AS '-2 Months', SUBDATE(CURDATE(), INTERVAL 2 QUARTER) AS '-2 Quarters', SUBDATE(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 – MySQL Subtract Hours from DateTime
Let’s subtract the time units from the given date/time value using the MySQL SUBDATE() function; as follows:
SELECT SUBDATE('2019-07-21 10:00:00', INTERVAL 4 HOUR) AS Result, SUBDATE(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 Value | Expected expr Format |
---|---|
MICROSECOND | MICROSECONDS |
SECOND | SECONDS |
MINUTE | MINUTES |
HOUR | HOURS |
DAY | DAYS |
WEEK | WEEKS |
MONTH | MONTHS |
QUARTER | QUARTERS |
YEAR | YEARS |
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
MySQL Subtract Days from DateTime tutorial, you have learned how to use MySQL SUBDATE() function with various examples.
Be First to Comment