diff options
Diffstat (limited to 'mysql-test/suite/engines/funcs/t/date_function.test')
| -rw-r--r-- | mysql-test/suite/engines/funcs/t/date_function.test | 412 |
1 files changed, 412 insertions, 0 deletions
diff --git a/mysql-test/suite/engines/funcs/t/date_function.test b/mysql-test/suite/engines/funcs/t/date_function.test new file mode 100644 index 00000000000..360dc898731 --- /dev/null +++ b/mysql-test/suite/engines/funcs/t/date_function.test @@ -0,0 +1,412 @@ +--disable_warnings +DROP TABLE IF EXISTS t3; +--enable_warnings +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DATE(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DATE(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DATE(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYNAME(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYNAME(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYNAME(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFMONTH(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFMONTH(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFMONTH(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFWEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFWEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFWEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFYEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFYEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT DAYOFYEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT FROM_DAYS(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT FROM_DAYS(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT FROM_DAYS(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT LAST_DAY(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT LAST_DAY(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT LAST_DAY(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT MONTH(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT MONTH(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT MONTH(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT MONTHNAME(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT MONTHNAME(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT MONTHNAME(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT QUARTER(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT QUARTER(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT QUARTER(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT TIMESTAMP(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT TIMESTAMP(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT TIMESTAMP(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT TO_DAYS(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT TO_DAYS(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT TO_DAYS(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEKDAY(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEKDAY(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEKDAY(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEKOFYEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEKOFYEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT WEEKOFYEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT YEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT YEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT YEAR(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATE NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT YEARWEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT YEARWEEK(c1) FROM t3; +DROP TABLE t3; +CREATE TABLE t3(c1 TIMESTAMP NOT NULL); +INSERT INTO t3 VALUES('1000-01-01'); +INSERT INTO t3 VALUES('1999-12-31'); +INSERT INTO t3 VALUES('2000-01-01'); +INSERT INTO t3 VALUES('2006-12-25'); +INSERT INTO t3 VALUES('2008-02-29'); +SELECT YEARWEEK(c1) FROM t3; +DROP TABLE t3; + |