mirror of
https://github.com/MariaDB/server.git
synced 2025-08-07 00:04:31 +03:00
39 lines
1.8 KiB
Plaintext
39 lines
1.8 KiB
Plaintext
--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;
|