Collections:
DATE_SUB() - Decrementing Date/Time
How to decrement a date or time value using the DATE_SUB() function?
✍: FYIcenter.com
DATE_SUB(date, INTERVAL expr unit) is a MySQL built-in function that decrements a date or time value with a given interval. For example:
SELECT DATE_SUB('2022-03-01', INTERVAL 1 DAY); -- +----------------------------------------+ -- | DATE_SUB('2022-03-01', INTERVAL 1 DAY) | -- +----------------------------------------+ -- | 2022-02-28 | -- +----------------------------------------+ SELECT DATE_SUB('2022-03-01', INTERVAL '1-1' YEAR_MONTH); -- +---------------------------------------------------+ -- | DATE_SUB('2022-03-01', INTERVAL '1-1' YEAR_MONTH) | -- +---------------------------------------------------+ -- | 2021-02-01 | -- +---------------------------------------------------+ SELECT DATE_SUB('2022-03-01 23:59:59', INTERVAL 1 SECOND); -- +----------------------------------------------------+ -- | DATE_SUB('2022-03-01 23:59:59', INTERVAL 1 SECOND) | -- +----------------------------------------------------+ -- | 2022-03-01 23:59:58 | -- +----------------------------------------------------+ SELECT DATE_SUB('2022-03-01', INTERVAL '1 2' DAY_HOUR); -- +-------------------------------------------------+ -- | DATE_SUB('2022-03-01', INTERVAL '1 2' DAY_HOUR) | -- +-------------------------------------------------+ -- | 2022-02-27 22:00:00 | -- +-------------------------------------------------+ SELECT DATE_SUB('2022-03-01', INTERVAL '-1.100' SECOND_MICROSECOND); -- +--------------------------------------------------------------+ -- | DATE_SUB('2022-03-01', INTERVAL '-1.100' SECOND_MICROSECOND) | -- +--------------------------------------------------------------+ -- | 2022-03-01 00:00:01.100000 | -- +--------------------------------------------------------------+
Reference information of the DATE_SUB() function:
DATE_SUB(date, INTERVAL expr unit): moddate Performs date and time subtraction operation. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be subtracted or added 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 date or time interval unit keyword. moddate: Return value. The decremented date and time. Available since MySQL 4.0. Date or time 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:
⇒ DAY() - Synonym for DAYOFMONTH()
⇐ DATE_FORMAT() - Formatting Date
2023-11-17, 251🔥, 0💬
Popular Posts:
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL? If you enter millise...