Test for ADD_MONTHS
CREATE TABLE t1(c1 int, c2 datetime, c3 date, c4 time, c5 timestamp);
INSERT INTO t1 VALUES (1, '2011-11-12 12:10:11', '2011-11-12', '12:10:11', '2011-11-12 12:10:11');
INSERT INTO t1 VALUES (2, '2021-11-12 00:23:12', '2021-11-12', '00:23:12', '2021-11-12 00:23:12');
INSERT INTO t1 VALUES (3, '2011-01-22 16:45:45', '2011-01-22', '16:45:45', '2011-01-22 16:45:45');
INSERT INTO t1 VALUES (4, '2031-05-12 04:11:34', '2031-05-12', '04:11:34', '2031-05-12 04:11:34');
INSERT INTO t1 VALUES (5, '2031-09-02 08:15:22', '2031-09-02', '08:15:22', '2031-09-02 08:15:22');
INSERT INTO t1 VALUES (6, '0000-09-02 00:00:00', '0000-09-02', '00:00:00', '1980-09-02 00:00:00');
INSERT INTO t1 VALUES (7, '9999-09-02', '9999-09-02', '00:00:00', '1980-09-02');
SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1;
c1	ADD_MONTHS(c2, 2)	ADD_MONTHS(c3, 2)	ADD_MONTHS(c5, 2)
1	2012-01-12 12:10:11	2012-01-12	2012-01-12 12:10:11
2	2022-01-12 00:23:12	2022-01-12	2022-01-12 00:23:12
3	2011-03-22 16:45:45	2011-03-22	2011-03-22 16:45:45
4	2031-07-12 04:11:34	2031-07-12	2031-07-12 04:11:34
5	2031-11-02 08:15:22	2031-11-02	2031-11-02 08:15:22
6	0000-11-02 00:00:00	0000-11-02	1980-11-02 00:00:00
7	9999-11-02 00:00:00	9999-11-02	1980-11-02 00:00:00
SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1;
c1	ADD_MONTHS(c2, 15)	ADD_MONTHS(c3, 200)	ADD_MONTHS(c5, 2000)
1	2013-02-12 12:10:11	2028-07-12	2178-07-12 12:10:11
2	2023-02-12 00:23:12	2038-07-12	2188-07-12 00:23:12
3	2012-04-22 16:45:45	2027-09-22	2177-09-22 16:45:45
4	2032-08-12 04:11:34	2048-01-12	2198-01-12 04:11:34
5	2032-12-02 08:15:22	2048-05-02	2198-05-02 08:15:22
6	0001-12-02 00:00:00	0017-05-02	2147-05-02 00:00:00
7	NULL	NULL	2147-05-02 00:00:00
Warnings:
Warning	1441	Datetime function: datetime field overflow
Warning	1441	Datetime function: datetime field overflow
SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1;
c1	ADD_MONTHS(c2, 0)	ADD_MONTHS(c3, -200)	ADD_MONTHS(c5, -2)
1	2011-11-12 12:10:11	1995-03-12	2011-09-12 12:10:11
2	2021-11-12 00:23:12	2005-03-12	2021-09-12 00:23:12
3	2011-01-22 16:45:45	1994-05-22	2010-11-22 16:45:45
4	2031-05-12 04:11:34	2014-09-12	2031-03-12 04:11:34
5	2031-09-02 08:15:22	2015-01-02	2031-07-02 08:15:22
6	0000-09-02 00:00:00	NULL	1980-07-02 00:00:00
7	9999-09-02 00:00:00	9983-01-02	1980-07-02 00:00:00
Warnings:
Warning	1441	Datetime function: datetime field overflow
SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1;
c1	ADD_MONTHS(c2, -15)	ADD_MONTHS(c3, -111)	ADD_MONTHS(c5, 2)
1	2010-08-12 12:10:11	2002-08-12	2012-01-12 12:10:11
2	2020-08-12 00:23:12	2012-08-12	2022-01-12 00:23:12
3	2009-10-22 16:45:45	2001-10-22	2011-03-22 16:45:45
4	2030-02-12 04:11:34	2022-02-12	2031-07-12 04:11:34
5	2030-06-02 08:15:22	2022-06-02	2031-11-02 08:15:22
6	NULL	NULL	1980-11-02 00:00:00
7	9998-06-02 00:00:00	9990-06-02	1980-11-02 00:00:00
Warnings:
Warning	1441	Datetime function: datetime field overflow
Warning	1441	Datetime function: datetime field overflow
SELECT ADD_MONTHS(c4, 11) FROM t1 WHERE c1 = 1;
ADD_MONTHS(c4, 11)
NULL
Warnings:
Warning	1441	Datetime function: time field overflow
UPDATE t1 SET c2=ADD_MONTHS(c2, 2);
SELECT c2 FROM t1;
c2
2012-01-12 12:10:11
2022-01-12 00:23:12
2011-03-22 16:45:45
2031-07-12 04:11:34
2031-11-02 08:15:22
0000-11-02 00:00:00
9999-11-02 00:00:00
EXPLAIN EXTENDED SELECT c1, ADD_MONTHS(c2, -15) FROM t1 WHERE c1 = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` + interval -15 month AS `ADD_MONTHS(c2, -15)` from `test`.`t1` where `test`.`t1`.`c1` = 1
SELECT ADD_MONTHS("2000-10-10", 12);
ADD_MONTHS("2000-10-10", 12)
2001-10-10
SELECT ADD_MONTHS("2000:10:10", 12);
ADD_MONTHS("2000:10:10", 12)
2001-10-10
SELECT ADD_MONTHS(2000, 12);
ADD_MONTHS(2000, 12)
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2000'
SELECT ADD_MONTHS('2011-01-31', 1), ADD_MONTHS('2012-01-31', 1), ADD_MONTHS('2012-01-31', 2), ADD_MONTHS('2012-01-31', 3);
ADD_MONTHS('2011-01-31', 1)	ADD_MONTHS('2012-01-31', 1)	ADD_MONTHS('2012-01-31', 2)	ADD_MONTHS('2012-01-31', 3)
2011-02-28	2012-02-29	2012-03-31	2012-04-30
SELECT ADD_MONTHS('2011-01-30', 1), ADD_MONTHS('2012-01-30', 1), ADD_MONTHS('2012-01-30', 2), ADD_MONTHS('2012-01-30', 3);
ADD_MONTHS('2011-01-30', 1)	ADD_MONTHS('2012-01-30', 1)	ADD_MONTHS('2012-01-30', 2)	ADD_MONTHS('2012-01-30', 3)
2011-02-28	2012-02-29	2012-03-30	2012-04-30
DROP TABLE t1;