In MariaDB, ADDDATE()
is a built-in date and time function that performs date arithmetic.
It allows you to change a date by specifying the date, the unit to add, and the amount to add. You can pass a negative amount if you need to subtract the date by a certain interval.
ADDDATE()
also has a shortcut syntax that allows you to add a given number of days to the date.
Syntax
The ADDDATE()
function has two syntaxes.
Syntax 1:
ADDDATE(expr,days)
Where expr
is the date, and days
is the number of days to add.
Syntax 2:
ADDDATE(date,INTERVAL expr unit)
Where date
is the date to change, expr
is the amount to add, and unit
is the unit to add (e.g. second, minute, etc).
When using this syntax, ADDDATE()
is a synonym for DATE_ADD()
.
Example – Syntax 1
Here’s an example of using the first syntax:
SELECT ADDDATE('2021-05-01', 1);
Result:
+--------------------------+ | ADDDATE('2021-05-01', 1) | +--------------------------+ | 2021-05-02 | +--------------------------+
We can also include the time portion if required:
SELECT ADDDATE('2021-05-01 10:00:00', 1);
Result:
+-----------------------------------+ | ADDDATE('2021-05-01 10:00:00', 1) | +-----------------------------------+ | 2021-05-02 10:00:00 | +-----------------------------------+
Here are two alternative ways of doing the same thing:
SELECT
DATE_ADD('2021-05-01 10:00:00', INTERVAL 1 DAY) AS "Result 1",
'2021-05-01 10:00:00' + INTERVAL 1 DAY AS "Result 2";
Result:
+---------------------+---------------------+ | Result 1 | Result 2 | +---------------------+---------------------+ | 2021-05-02 10:00:00 | 2021-05-02 10:00:00 | +---------------------+---------------------+
Example – Syntax 2
Here’s an example of using the second syntax:
SELECT ADDDATE('2021-05-31 10:00:00', INTERVAL 1 HOUR);
Result:
+-------------------------------------------------+ | ADDDATE('2021-05-31 10:00:00', INTERVAL 1 HOUR) | +-------------------------------------------------+ | 2021-05-31 11:00:00 | +-------------------------------------------------+
This syntax allows us to add other units to the date (i.e. not just the days). Here, I added an hour to the date, but I could just as easily have added minutes, seconds, months, days, years, etc. Continue reading for examples.
Here are two alternative methods for achieving the same outcome as the above example:
SELECT
DATE_ADD('2021-05-31 10:00:00', INTERVAL 1 HOUR) AS "Result 1",
'2021-05-31 10:00:00' + INTERVAL 1 HOUR AS "Result 2";
Result:
+---------------------+---------------------+ | Result 1 | Result 2 | +---------------------+---------------------+ | 2021-05-31 11:00:00 | 2021-05-31 11:00:00 | +---------------------+---------------------+
Negative Intervals
Providing a negative interval subtracts that amount from the date.
Example:
SELECT ADDDATE('2021-05-31 10:00:00', INTERVAL -1 HOUR);
Result:
+--------------------------------------------------+ | ADDDATE('2021-05-31 10:00:00', INTERVAL -1 HOUR) | +--------------------------------------------------+ | 2021-05-31 09:00:00 | +--------------------------------------------------+
Other Units
Here’s an example that adds an interval of 1 to the various date and time units:
SELECT
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 YEAR) AS YEAR,
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 MONTH) AS MONTH,
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 DAY) AS DAY,
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 HOUR) AS HOUR,
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 MINUTE) AS MINUTE,
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 SECOND) AS SECOND,
ADDDATE('2021-05-01 10:00:00', INTERVAL 1 MICROSECOND) AS MICROSECOND;
Result (using vertical output):
YEAR: 2022-05-01 10:00:00 MONTH: 2021-06-01 10:00:00 DAY: 2021-05-02 10:00:00 HOUR: 2021-05-01 11:00:00 MINUTE: 2021-05-01 10:01:00 SECOND: 2021-05-01 10:00:01 MICROSECOND: 2021-05-01 10:00:00.000001
Composite Units
Here’s an example that uses composite units:
SELECT
ADDDATE('2021-05-01 10:00:00', INTERVAL '1:2' YEAR_MONTH) AS "YEAR_MONTH",
ADDDATE('2021-05-01 10:00:00', INTERVAL '1:25:35' HOUR_SECOND) AS "HOUR_SECOND",
ADDDATE('2021-05-01 10:00:00', INTERVAL '1:30' DAY_MINUTE) AS "DAY_MINUTE";
Result:
+---------------------+---------------------+---------------------+ | YEAR_MONTH | HOUR_SECOND | DAY_MINUTE | +---------------------+---------------------+---------------------+ | 2022-07-01 10:00:00 | 2021-05-01 11:25:35 | 2021-05-01 11:30:00 | +---------------------+---------------------+---------------------+
Null Dates
Passing null
for the date returns null
:
SELECT ADDDATE(null, INTERVAL 1 YEAR);
Result:
+--------------------------------+ | ADDDATE(null, INTERVAL 1 YEAR) | +--------------------------------+ | NULL | +--------------------------------+
Missing Argument
Calling ADDDATE()
with the wrong number of arguments, or without passing any arguments results in an error:
SELECT ADDDATE();
Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1