Collections:
DATE_ADD() - Adding Date/Time Interval
How to update a datetime value by adding a date/time interval using the DATE_ADD() function?
✍: FYIcenter.com
DATE_ADD(date, INTERVAL expr unit) is a MySQL built-in function that
updates a date or time value with a given interval.
For example:
SELECT DATE_ADD('2022-02-28', INTERVAL 1 DAY);
-- +----------------------------------------+
-- | DATE_ADD('2022-02-28', INTERVAL 1 DAY) |
-- +----------------------------------------+
-- | 2022-03-01 |
-- +----------------------------------------+
SELECT DATE_ADD('2022-02-28', INTERVAL '1-1' YEAR_MONTH);
-- +---------------------------------------------------+
-- | DATE_ADD('2022-02-28', INTERVAL '1-1' YEAR_MONTH) |
-- +---------------------------------------------------+
-- | 2023-03-28 |
-- +---------------------------------------------------+
SELECT DATE_ADD('2022-02-28 23:59:59', INTERVAL 1 SECOND);
-- +----------------------------------------------------+
-- | DATE_ADD('2022-02-28 23:59:59', INTERVAL 1 SECOND) |
-- +----------------------------------------------------+
-- | 2022-03-01 00:00:00 |
-- +----------------------------------------------------+
SELECT DATE_ADD('2022-02-28', INTERVAL '1 2' DAY_HOUR);
-- +-------------------------------------------------+
-- | DATE_ADD('2022-02-28', INTERVAL '1 2' DAY_HOUR) |
-- +-------------------------------------------------+
-- | 2022-03-01 02:00:00 |
-- +-------------------------------------------------+
SELECT DATE_ADD('2022-02-28', INTERVAL '-1.100' SECOND_MICROSECOND);
-- +--------------------------------------------------------------+
-- | DATE_ADD('2022-02-28', INTERVAL '-1.100' SECOND_MICROSECOND) |
-- +--------------------------------------------------------------+
-- | 2022-02-27 23:59:58.900000 |
-- +--------------------------------------------------------------+
Reference information of the DATE_ADD() function:
DATE_ADD(date, INTERVAL expr unit): moddate Performs date and time addition operation. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is evaluated as a string; it may start with a - for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. Arguments, return value and availability: date: Required. The starting date and time. expr: Required. The date or time interval. unit: Required. The datetime interval unit keyword. moddate: Return value. The incremented date and time. Available since MySQL 4.0. Datetime interval unit keywords: Unit keyword Examples ------------------ -------- MICROSECOND 100, -100 SECOND 100, -100 MINUTE 100, -100 HOUR 100, -100 DAY 100, -100 WEEK 100, -100 MONTH 100, -100 QUARTER 100, -100 YEAR 100, -100 SECOND_MICROSECOND '10.100', '-10.100' MINUTE_MICROSECOND '10:10.100', '-10:10.100' MINUTE_SECOND '10.10', '-10.10' HOUR_MICROSECOND '10:10:10.100', '-10:10:10.100' HOUR_SECOND '10:10.10', '-10:10.10' HOUR_MINUTE '10.10', '-10.10' DAY_MICROSECOND '100 10:10:10.100', '-100 10:10:10.100' DAY_SECOND '100 10:10:10', '-100 10:10:10' DAY_MINUTE '100 10:10', '-100 10:10' DAY_HOUR '100 10', '-100 10' YEAR_MONTH '100-100', '-100-100'
Related MySQL functions:
⇒ DATE_FORMAT() - Formatting Date
⇐ DATEDIFF() - Difference of Two Dates
2023-11-17, 1902🔥, 0💬
Popular Posts:
How to format a number of bytes in a human-readable unit using the FORMAT_BYTES() function? FORMAT_B...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...