MySQL DATE_ADD() Function

Syntax of MySQL DATE_ADD() Function

The syntax for the add date in MySQL is:

Parameters of MySQL DATE_ADD() Function

The add date in MySQL requires three parameters:

1. The first parameter is the date or datetime value to which you want to add the interval.
   • date : input date or datetime value
2. The second parameter is the interval you want to add to the date or datetime value.
   • value : number of intervals to add
3. The third parameter specifies the unit of the interval.
   • unit: unit of the interval (such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.)

Return Value of MySQL DATE_ADD() Function

The add date in MySQL returns a new date or datetime value after adding the specified interval to the input date or datetime value. The return type depends on the input type.

If the input is a DATE value, then the DATE_ADD() function returns a DATE value. If the input is a DATETIME value, then the DATE_ADD() function returns a DATETIME value. If the input is a DATE and the unit value uses HOURS, MINUTES, or SECONDS, then the function will return a DATETIME value.

If the resulting date or datetime value is out of range or invalid, the DATE_ADD() function may return NULL or a different value depending on the SQL mode and server settings.

Exceptions of MySQL DATE_ADD() Function

Here are some of the common scenarios in which the add date in MySQL may not work as expected:

• Date format mismatch: If the date format in the DATE_ADD() function argument does not match the expected format, it may not work as expected and return a NULL value.
• Time zone mismatch: If the input date and the time interval are in different time zones, the DATE_ADD() function may not work as expected. This can result in an incorrect date calculation.
• Overflow or underflow: If the date calculation exceeds the maximum or minimum value that can be represented in the database, an overflow or underflow error may occur.

How does the MySQL DATE_ADD() Function Work?

The add date in MySQL is used to add a specified time interval to a given date or datetime value. Here's how it works internally:

• The time interval can be specified in various formats, such as the number of seconds, minutes, hours, days, weeks, months, or years. It can also be specified as a combination of these units, using the INTERVAL keyword.
• Once the function receives the initial date value and the time interval, it calculates the new date or datetime value by adding the time interval to the initial value.
• Internally, the function uses the TIMESTAMPADD() function to perform the calculation.
• The TIMESTAMPADD() function then performs the calculation by adding the time interval to the initial value and returns the new date or datetime value.
• The DATE_ADD() function then returns this new date or datetime value as its result.

Examples Here are some examples of using the add date in MySQL:

1. Adding Days to a Date Code:

   Output:

   Explanation: In this example, the DATE_ADD() function is used to add 7 days to the date '2023-03-17'.

2. Adding Months to a Date Code:

   Output:

   Explanation: In this example, the function is used to add 3 months to the datetime '2022-03-21 12:34:56'.

3. Adding Years to a Date Code:

   Output:

   Explanation: In this example, the function is used to add 2 years to the date '2023-03-17'.

4. Adding a negative interval to a date (subtraction): Code:

   Output:

   Explanation: In the example, the function subtracts 1 year from the date '2022-03-17' by specifying -1 YEAR. The negative sign - indicates that the interval is subtracted from the date, rather than added to it.

5. If the input is a DATE and TIMESTAMP UNIT is passed, then the function will return a DATETIME value Code:

   Output:

   Explanation: In this example, the function adds '01:01' HOUR_MINUTE to the date '2000-01-01' and returns output in DateTime format.

See Also

Here are some related functions that you might find useful about the DATE_ADD() function in MySQL:

• DATEDIFF() function
• DATE_SUB() function