1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
--echo 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');
# some normal case
SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1;
SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1;
SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1;
SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1;
# for time type, it will be overflow
SELECT ADD_MONTHS(c4, 11) FROM t1 WHERE c1 = 1;
UPDATE t1 SET c2=ADD_MONTHS(c2, 2);
SELECT c2 FROM t1;
EXPLAIN EXTENDED SELECT c1, ADD_MONTHS(c2, -15) FROM t1 WHERE c1 = 1;
# string type can be convert to datetime type
SELECT ADD_MONTHS("2000-10-10", 12);
SELECT ADD_MONTHS("2000:10:10", 12);
# number type can not be convert datetime type
SELECT ADD_MONTHS(2000, 12);
# last day of the month
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);
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);
DROP TABLE t1;
|
