This tutorial explains MySQL DATE_ADD function which adds up a slice of time (in days/hours, e.g., 1 day or 10 days) to the given date. We’ll describe the complete date arithmetic of this method with the help of simple examples.
1. DATE_ADD() Syntax
2. DATE_ADD() with -ve Interval
3. DATE_ADD() for Invalid Date
4. DATE_ADD() for Auto Adjustment
Let’s now go through each of the section one by one.
MySQL DATE_ADD() Function
As stated initially, DATE_ADD() is a built-in MySQL function which adds the specified no. of days to a given date. So, let’s now see the details and check out how can we use it.
Syntax
Below is the signature of this method:
DATE_ADD(Given_date, INTERVAL expr unit);
Below are the descriptions of the parameters in the above function.
+-------------+-----------------------------------------+ | # Params | # Description | +-------------+-----------------------------------------+ | Given_date | Date value of DATE or DATETIME type. | | expr | String representing time interval value.| +-------------+-----------------------------------------+
Let’s take an example which demonstrates the DATE_ADD() function to plus 2 days from the current date.
SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY) result;
In the above statement, we’ve called the MySQL CURDATE() function which returns the date of the present day. Anyways, after executing this command, it gives the following output:
1 MySQL Workbench 2019-07-12
The current date was “2019-07-10” at the time of execution. Hence, after adding two days, the result came out as above.
After looking at the arguments, let’s check out what does the DATE_ADD() function returns.
- The return value would also be a DATE when the input value was of the DATE type.
- It would return a DATETIME if the input had time in hours, minutes, and seconds.
- A string value is sent back in all other cases.
Below is an example which calls the DATE_ADD() function to add in terms of hours.
SELECT CURDATE(); SELECT DATE_ADD(CURDATE(), INTERVAL 6 HOUR) result;
After executing the above MySQL command, the result comes as:
1 MySQL Workbench 2019-07-10 2019-07-10 06:00:00
Related Topic – MySQL Date and Date Functions
MySQL DATE_ADD() Examples
Let’s now unveil several examples addressing different situations.
Using MySQL DATE_ADD with -ve Interval
The second argument (expr) of the DATE_ADD() function can also have -ve interval values.
However, if we pass a negative interval value, then it will act like the MySQL DATE_SUB() function. You will get more clarity by seeing the below example.
SELECT CURDATE(); SELECT DATE_ADD(CURDATE(), INTERVAL -2 day) result;
Since the current date was “2019-07-10” at the time of execution, so the above command subtracted two days in the final result. Check the result/outcome below.
1 MySQL Workbench 2019-07-10 2019-07-08
Calling MySQL DATE_ADD for Invalid Date
It is a negative case when you pass an invalid or malformed or NULL date value to the DATE_ADD(). This function responds to such inputs by returning a NULL.
SELECT DATE_ADD('2019-13-09', INTERVAL 2 day) result;
The date “2019-13-09” is an incorrect value as the month can’t go beyond 12. Hence, after executing the above command, the output will be:
1 MySQL Workbench NULL
The MySQL system would also notice this operation and throw a warning. We can track the same by issuing the below command:
SHOW WARNINGS;
The result/output:
Warning 1292 Incorrect datetime value: '2019-13-09'
Now, let’s have one more example to observe the behavior of MySQL DATE_ADD() function for a malformed date.
-- Input date is malformed
SELECT DATE_ADD('2019-07-09-2019', INTERVAL 2 day) result;
-- Input date is NULL
SELECT DATE_ADD(NULL, INTERVAL 2 day) result;
After running the above MySQL statements, you should see the following outcome:
1 MySQL Workbench NULL NULL
Related Topic – Install MySQL on Ubuntu and Debian OS
MySQL DATE_ADD for Auto Adjustment
The DATE_ADD() function automatically adjust the output in the following situation:
- After adding the interval (MONTH, YEAR, or YEAR_MONTH), the result comes to a date that surpasses the maximum day of the new month.
In this scenario, the function normalizes the final date to the last day of the new month. You can observe yourself by running through the example below:
-- Passing '2019-07-31' as the edge date value
SELECT DATE_ADD('2019-01-31', INTERVAL 1 MONTH) result;
After execution, we get this:
1 MySQL Workbench 2019-02-28
In this example, we forwarded the date by 1 month from Jan 31st, 2019. Therefore, the output comes to be Feb 28th, 2019. The day was automatically normalized to Feb, 28th instead of jumping over.
We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL DATE_ADD() function. However, you may practice more with examples to gain confidence.
Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.
