{"id":2602,"date":"2018-06-20T18:15:02","date_gmt":"2018-06-21T01:15:02","guid":{"rendered":"https:\/\/database.guide\/?p=2602"},"modified":"2021-01-28T14:11:23","modified_gmt":"2021-01-28T04:11:23","slug":"adddate-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/adddate-examples-mysql\/","title":{"rendered":"ADDDATE() Examples – MySQL"},"content":{"rendered":"

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

The ADDDATE()<\/code> function is a synonym for the DATE_ADD()<\/code><\/a> function (they both do the same thing) when using the first syntax listed below.<\/p>\n

This article contains examples to demonstrate usage of the ADDDATE()<\/code> function.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

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

ADDDATE(date,INTERVAL expr unit)<\/pre>\n
Or<\/p>\n
ADDDATE(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 ADDDATE('2018-12-01', INTERVAL 2 DAY) AS Result;<\/pre>\n
Result:<\/p>\n
+------------+\r\n| Result     |\r\n+------------+\r\n| 2018-12-03 |\r\n+------------+<\/pre>\n
This example adds 2 days to the date supplied by the first argument.<\/p>\n
Example 2 – The Second Syntax<\/h2>\n
This example could be rewritten to the following:<\/p>\n
SELECT ADDDATE('2018-12-01', 2) AS Result;<\/pre>\n
Result:<\/p>\n
+------------+\r\n| Result     |\r\n+------------+\r\n| 2018-12-03 |\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 added to the date supplied by the first argument. So obviously, this form is only suitable if you want to specify the amount in days.<\/p>\n
Note that\u00a0ADDDATE()<\/code> is a synonym for DATE_ADD()<\/code>\u00a0only when the first syntax is being used. The second syntax is only available in\u00a0ADDDATE()<\/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 add days, weeks, months, years, etc. Here are some examples.<\/p>\n
SELECT \r\n    '2018-12-01' AS 'Start Date',\r\n    ADDDATE('2018-12-01', INTERVAL 2 WEEK) AS '+2 Weeks',\r\n    ADDDATE('2018-12-01', INTERVAL 2 MONTH) AS '+2 Months',\r\n    ADDDATE('2018-12-01', INTERVAL 2 QUARTER) AS '+2 Quarters',\r\n    ADDDATE('2018-12-01', 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-12-01 | 2018-12-15 | 2019-02-01 | 2019-06-01  | 2020-12-01 |\r\n+------------+------------+------------+-------------+------------+<\/pre>\n
Example 4 – Time Units<\/h2>\n
You can also use ADDDATE()<\/code> to add time units to a date\/time value. Here’s an example.<\/p>\n
SELECT ADDDATE('2018-12-01 01:00:00', INTERVAL 2 HOUR) AS Result;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+\r\n| Result              |\r\n+---------------------+\r\n| 2018-12-01 03: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 ADDDATE() function to add a specified amount of time to a date. For example, you could use it to add 10 days to a given date. You can specify whether to add days, weeks, months, quarters, years, etc. You can also add 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-2602","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\/2602","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=2602"}],"version-history":[{"count":15,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2602\/revisions"}],"predecessor-version":[{"id":2623,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2602\/revisions\/2623"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}