{"id":2626,"date":"2018-06-20T21:33:42","date_gmt":"2018-06-21T04:33:42","guid":{"rendered":"https:\/\/database.guide\/?p=2626"},"modified":"2021-01-28T14:11:23","modified_gmt":"2021-01-28T04:11:23","slug":"subdate-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/subdate-examples-mysql\/","title":{"rendered":"SUBDATE() Examples – MySQL"},"content":{"rendered":"

In MySQL<\/a>, you can use the SUBDATE()<\/code> function to subtract a specified amount of time from a date. For example, you could use it to subtract 10 days from a given date. You can specify whether to subtract days, weeks, months, quarters, years, etc. You can also subtract a time value, such as seconds, microseconds, etc.<\/p>\n

When using the first syntax listed below, the SUBDATE()<\/code> function is a synonym for the DATE_SUB()<\/code><\/a> function (similar to how ADDDATE()<\/code><\/a> is a synonym for DATE_ADD()<\/code><\/a> when using the same syntax).<\/p>\n

<\/p>\n

Syntax<\/h2>\n

You can use this function in the following two ways:<\/p>\n

SUBDATE(date,INTERVAL expr unit)<\/pre>\n
Or<\/p>\n
SUBDATE(expr,days)<\/pre>\n
Example 1 – The First Syntax<\/h2>\n
Here’s an example of using the first form of the syntax.<\/p>\n
SELECT SUBDATE('2018-05-10', INTERVAL 2 DAY) AS Result;<\/pre>\n
Result:<\/p>\n
+------------+\r\n| Result     |\r\n+------------+\r\n| 2018-05-08 |\r\n+------------+<\/pre>\n
This example subtracts 2 days from the date supplied by the first argument.<\/p>\n
Example 2 – The Second Syntax<\/h2>\n
This example could be rewritten as the following:<\/p>\n
SELECT SUBDATE('2018-05-10', 2) AS Result;<\/pre>\n
Result:<\/p>\n
+------------+\r\n| Result     |\r\n+------------+\r\n| 2018-05-08 |\r\n+------------+<\/pre>\n
This uses the second form of the syntax. The second argument is an integer that represents how many days should be subtracted from the date supplied by the first argument.<\/p>\n
As mentioned,\u00a0SUBDATE()<\/code> is a synonym for DATE_SUB()<\/code>, but\u00a0only when the first syntax is being used. The second syntax is only available in\u00a0SUBDATE()<\/code>.<\/p>\n
Example 3 – Other Date Units<\/h2>\n
One benefit of the first form of the syntax is that you can specify whether to subtract days, weeks, months, years, etc. Here are some examples.<\/p>\n
SELECT \r\n    '2018-05-10' AS 'Start Date',\r\n    SUBDATE('2018-05-10', INTERVAL 2 WEEK) AS '-2 Weeks',\r\n    SUBDATE('2018-05-10', INTERVAL 2 MONTH) AS '-2 Months',\r\n    SUBDATE('2018-05-10', INTERVAL 2 QUARTER) AS '-2 Quarters',\r\n    SUBDATE('2018-05-10', INTERVAL 2 YEAR) AS '-2 Years';<\/pre>\n
Result:<\/p>\n
+------------+------------+------------+-------------+------------+\r\n| Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |\r\n+------------+------------+------------+-------------+------------+\r\n| 2018-05-10 | 2018-04-26 | 2018-03-10 | 2017-11-10  | 2016-05-10 |\r\n+------------+------------+------------+-------------+------------+<\/pre>\n
Example 4 – Time Units<\/h2>\n
You can also use SUBDATE()<\/code> to subtract time units from a date\/time value. Here’s an example.<\/p>\n
SELECT SUBDATE('2018-05-10 01:00:00', INTERVAL 2 HOUR) AS Result;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+\r\n| Result              |\r\n+---------------------+\r\n| 2018-05-09 23:00:00 |\r\n+---------------------+<\/pre>\n
Expected Values<\/h2>\n
The following table shows the valid unit values and their expected format.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
unit<\/code> Value<\/th>\nExpected expr<\/code> Format<\/th>\n<\/tr>\n<\/thead>\n
MICROSECOND<\/td>\nMICROSECONDS<\/td>\n<\/tr>\n
SECOND<\/td>\nSECONDS<\/td>\n<\/tr>\n
MINUTE<\/td>\nMINUTES<\/td>\n<\/tr>\n
HOUR<\/td>\nHOURS<\/td>\n<\/tr>\n
DAY<\/td>\nDAYS<\/td>\n<\/tr>\n
WEEK<\/td>\nWEEKS<\/td>\n<\/tr>\n
MONTH<\/td>\nMONTHS<\/td>\n<\/tr>\n
QUARTER<\/td>\nQUARTERS<\/td>\n<\/tr>\n
YEAR<\/td>\nYEARS<\/td>\n<\/tr>\n
SECOND_MICROSECOND<\/td>\n‘SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
MINUTE_MICROSECOND<\/td>\n‘MINUTES:SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
MINUTE_SECOND<\/td>\n‘MINUTES:SECONDS’<\/td>\n<\/tr>\n
HOUR_MICROSECOND<\/td>\n‘HOURS:MINUTES:SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
HOUR_SECOND<\/td>\n‘HOURS:MINUTES:SECONDS’<\/td>\n<\/tr>\n
HOUR_MINUTE<\/td>\n‘HOURS:MINUTES’<\/td>\n<\/tr>\n
DAY_MICROSECOND<\/td>\n‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’<\/td>\n<\/tr>\n
DAY_SECOND<\/td>\n‘DAYS HOURS:MINUTES:SECONDS’<\/td>\n<\/tr>\n
DAY_MINUTE<\/td>\n‘DAYS HOURS:MINUTES’<\/td>\n<\/tr>\n
DAY_HOUR<\/td>\n‘DAYS HOURS’<\/td>\n<\/tr>\n
YEAR_MONTH<\/td>\n‘YEARS-MONTHS’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
In MySQL, you can use the SUBDATE() function to subtract a specified amount of time from a date. For example, you could use it to subtract 10 days from a given date. You can specify whether to subtract days, weeks, months, quarters, years, etc. You can also subtract a time value, such as seconds, microseconds, … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2626","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2626","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2626"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2626\/revisions"}],"predecessor-version":[{"id":2635,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2626\/revisions\/2635"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}