Repository navigation
DATE_ADD() and DATE_SUB() overflow past the end of the month, and ADDDATE() and SUBDATE() are not supported #531
Description
Reproduction
CREATE TABLE s (id INT PRIMARY KEY, renews_on DATE);
INSERT INTO s VALUES (1, '2026-01-31');
-- 1. Month and year arithmetic overflows instead of clamping to the last day of the month.
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- '2024-03-02' (MySQL: '2024-02-29')
SELECT DATE_ADD('2023-01-31 08:00:00', INTERVAL 1 MONTH); -- '2023-03-03 08:00:00' (MySQL: '2023-02-28 08:00:00')
SELECT DATE_ADD('2024-02-29', INTERVAL 1 YEAR); -- '2025-03-01' (MySQL: '2025-02-28')
UPDATE s SET renews_on = DATE_ADD(renews_on, INTERVAL 1 MONTH);
-- stores '2026-03-03' (MySQL: '2026-02-28')
-- 2. ADDDATE() and SUBDATE() are not supported.
SELECT ADDDATE('2026-07-13', INTERVAL 7 DAY); -- syntax error (MySQL: '2026-07-20')
SELECT ADDDATE('2026-07-13', 7); -- no such function (MySQL: '2026-07-20')
SELECT SUBDATE('2026-07-13', 7); -- no such function (MySQL: '2026-07-06')
SELECT SUBDATE('2026-07-13', INTERVAL 1 WEEK); -- syntax error (MySQL: '2026-07-06')Reproduced on trunk (v3.1.1) with PHP 8.3 and SQLite 3.45.1.
The first case silently stores wrong data: a monthly renewal from January 31 lands on March 3.
Notes
MySQL adjusts the day to the last day of the resulting month when MONTH, QUARTER, or YEAR arithmetic produces a day that doesn't exist in it. ADDDATE(date, INTERVAL ...) is a synonym for DATE_ADD(), ADDDATE(date, days) adds days, and the same applies to SUBDATE().
This issue originally also covered a DATE input returning a DATETIME, and QUARTER and composite units returning NULL. #539 fixed the first two: date-only inputs now return a DATE, and QUARTER works. Composite units such as HOUR_MINUTE are now rejected with an error instead of returning NULL, which is out of scope here.
I'm happy to contribute a PR for the month-end clamping and for ADDDATE() and SUBDATE().
Use of AI
This report was prepared with Claude Code.
Activity
Return a date for date-only interval arithmetic
Support date arithmetic with `INTERVAL` operators (#539)
[-]DATE_ADD() and DATE_SUB() differ from MySQL for DATE values, month ends, and QUARTER and composite units[/-][+]DATE_ADD() and DATE_SUB() overflow past the end of the month, and ADDDATE() and SUBDATE() are not supported[/+]Nandan108 commentedon Oct 23, 2026
Narrowed this issue after #539 and v3.1.1: the
DATEresult type andQUARTERcases are fixed, composite units now fail with an error instead of returningNULL, and the month-end overflow andADDDATE()/SUBDATE()remain. I re-ran every example ontrunkbefore editing.