Edit File: date_function.test
--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;