Debian Codegraph @

mysql-test/suite/compat/oracle/t/func_add_months.test

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