Debian Codegraph @

mysql-test/suite/compat/oracle/r/func_add_months.result

raw
1 Test for ADD_MONTHS
2 CREATE TABLE t1(c1 int, c2 datetime, c3 date, c4 time, c5 timestamp);
3 INSERT INTO t1 VALUES (1, '2011-11-12 12:10:11', '2011-11-12', '12:10:11', '2011-11-12 12:10:11');
4 INSERT INTO t1 VALUES (2, '2021-11-12 00:23:12', '2021-11-12', '00:23:12', '2021-11-12 00:23:12');
5 INSERT INTO t1 VALUES (3, '2011-01-22 16:45:45', '2011-01-22', '16:45:45', '2011-01-22 16:45:45');
6 INSERT INTO t1 VALUES (4, '2031-05-12 04:11:34', '2031-05-12', '04:11:34', '2031-05-12 04:11:34');
7 INSERT INTO t1 VALUES (5, '2031-09-02 08:15:22', '2031-09-02', '08:15:22', '2031-09-02 08:15:22');
8 INSERT INTO t1 VALUES (6, '0000-09-02 00:00:00', '0000-09-02', '00:00:00', '1980-09-02 00:00:00');
9 INSERT INTO t1 VALUES (7, '9999-09-02', '9999-09-02', '00:00:00', '1980-09-02');
10 SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1;
11 c1 ADD_MONTHS(c2, 2) ADD_MONTHS(c3, 2) ADD_MONTHS(c5, 2)
12 1 2012-01-12 12:10:11 2012-01-12 2012-01-12 12:10:11
13 2 2022-01-12 00:23:12 2022-01-12 2022-01-12 00:23:12
14 3 2011-03-22 16:45:45 2011-03-22 2011-03-22 16:45:45
15 4 2031-07-12 04:11:34 2031-07-12 2031-07-12 04:11:34
16 5 2031-11-02 08:15:22 2031-11-02 2031-11-02 08:15:22
17 6 0000-11-02 00:00:00 0000-11-02 1980-11-02 00:00:00
18 7 9999-11-02 00:00:00 9999-11-02 1980-11-02 00:00:00
19 SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1;
20 c1 ADD_MONTHS(c2, 15) ADD_MONTHS(c3, 200) ADD_MONTHS(c5, 2000)
21 1 2013-02-12 12:10:11 2028-07-12 2178-07-12 12:10:11
22 2 2023-02-12 00:23:12 2038-07-12 2188-07-12 00:23:12
23 3 2012-04-22 16:45:45 2027-09-22 2177-09-22 16:45:45
24 4 2032-08-12 04:11:34 2048-01-12 2198-01-12 04:11:34
25 5 2032-12-02 08:15:22 2048-05-02 2198-05-02 08:15:22
26 6 0001-12-02 00:00:00 0017-05-02 2147-05-02 00:00:00
27 7 NULL NULL 2147-05-02 00:00:00
28 Warnings:
29 Warning 1441 Datetime function: datetime field overflow
30 Warning 1441 Datetime function: datetime field overflow
31 SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1;
32 c1 ADD_MONTHS(c2, 0) ADD_MONTHS(c3, -200) ADD_MONTHS(c5, -2)
33 1 2011-11-12 12:10:11 1995-03-12 2011-09-12 12:10:11
34 2 2021-11-12 00:23:12 2005-03-12 2021-09-12 00:23:12
35 3 2011-01-22 16:45:45 1994-05-22 2010-11-22 16:45:45
36 4 2031-05-12 04:11:34 2014-09-12 2031-03-12 04:11:34
37 5 2031-09-02 08:15:22 2015-01-02 2031-07-02 08:15:22
38 6 0000-09-02 00:00:00 NULL 1980-07-02 00:00:00
39 7 9999-09-02 00:00:00 9983-01-02 1980-07-02 00:00:00
40 Warnings:
41 Warning 1441 Datetime function: datetime field overflow
42 SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1;
43 c1 ADD_MONTHS(c2, -15) ADD_MONTHS(c3, -111) ADD_MONTHS(c5, 2)
44 1 2010-08-12 12:10:11 2002-08-12 2012-01-12 12:10:11
45 2 2020-08-12 00:23:12 2012-08-12 2022-01-12 00:23:12
46 3 2009-10-22 16:45:45 2001-10-22 2011-03-22 16:45:45
47 4 2030-02-12 04:11:34 2022-02-12 2031-07-12 04:11:34
48 5 2030-06-02 08:15:22 2022-06-02 2031-11-02 08:15:22
49 6 NULL NULL 1980-11-02 00:00:00
50 7 9998-06-02 00:00:00 9990-06-02 1980-11-02 00:00:00
51 Warnings:
52 Warning 1441 Datetime function: datetime field overflow
53 Warning 1441 Datetime function: datetime field overflow
54 SELECT ADD_MONTHS(c4, 11) FROM t1 WHERE c1 = 1;
55 ADD_MONTHS(c4, 11)
56 NULL
57 Warnings:
58 Warning 1441 Datetime function: time field overflow
59 UPDATE t1 SET c2=ADD_MONTHS(c2, 2);
60 SELECT c2 FROM t1;
61 c2
62 2012-01-12 12:10:11
63 2022-01-12 00:23:12
64 2011-03-22 16:45:45
65 2031-07-12 04:11:34
66 2031-11-02 08:15:22
67 0000-11-02 00:00:00
68 9999-11-02 00:00:00
69 EXPLAIN EXTENDED SELECT c1, ADD_MONTHS(c2, -15) FROM t1 WHERE c1 = 1;
70 id select_type table type possible_keys key key_len ref rows filtered Extra
71 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where
72 Warnings:
73 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
74 SELECT ADD_MONTHS("2000-10-10", 12);
75 ADD_MONTHS("2000-10-10", 12)
76 2001-10-10
77 SELECT ADD_MONTHS("2000:10:10", 12);
78 ADD_MONTHS("2000:10:10", 12)
79 2001-10-10
80 SELECT ADD_MONTHS(2000, 12);
81 ADD_MONTHS(2000, 12)
82 NULL
83 Warnings:
84 Warning 1292 Incorrect datetime value: '2000'
85 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);
86 ADD_MONTHS('2011-01-31', 1) ADD_MONTHS('2012-01-31', 1) ADD_MONTHS('2012-01-31', 2) ADD_MONTHS('2012-01-31', 3)
87 2011-02-28 2012-02-29 2012-03-31 2012-04-30
88 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);
89 ADD_MONTHS('2011-01-30', 1) ADD_MONTHS('2012-01-30', 1) ADD_MONTHS('2012-01-30', 2) ADD_MONTHS('2012-01-30', 3)
90 2011-02-28 2012-02-29 2012-03-30 2012-04-30
91 DROP TABLE t1;