1SET DEFAULT_STORAGE_ENGINE='tokudb'; 2drop table if exists t1,t2,t3; 3create table t1 (a char(16), b date, c datetime); 4insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01'; 5select * from t1 where c = '2000-01-01'; 6a b c 7test 2000-01-01 2000-01-01 2000-01-01 00:00:00 8select * from t1 where b = '2000-01-01'; 9a b c 10test 2000-01-01 2000-01-01 2000-01-01 00:00:00 11drop table t1; 12CREATE TABLE t1 (name char(6),cdate date); 13INSERT INTO t1 VALUES ('name1','1998-01-01'); 14INSERT INTO t1 VALUES ('name2','1998-01-01'); 15INSERT INTO t1 VALUES ('name1','1998-01-02'); 16INSERT INTO t1 VALUES ('name2','1998-01-02'); 17CREATE TABLE t2 (cdate date, note char(6)); 18INSERT INTO t2 VALUES ('1998-01-01','note01'); 19INSERT INTO t2 VALUES ('1998-01-02','note02'); 20select name,t1.cdate,note from t1,t2 where t1.cdate=t2.cdate and t1.cdate='1998-01-01'; 21name cdate note 22name1 1998-01-01 note01 23name2 1998-01-01 note01 24drop table t1,t2; 25CREATE TABLE t1 ( datum DATE ); 26INSERT INTO t1 VALUES ( "2000-1-1" ); 27INSERT INTO t1 VALUES ( "2000-1-2" ); 28INSERT INTO t1 VALUES ( "2000-1-3" ); 29INSERT INTO t1 VALUES ( "2000-1-4" ); 30INSERT INTO t1 VALUES ( "2000-1-5" ); 31SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); 32datum 332000-01-02 342000-01-03 352000-01-04 36SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; 37datum 38DROP TABLE t1; 39CREATE TABLE t1 ( 40user_id char(10), 41summa int(11), 42rdate date 43); 44INSERT INTO t1 VALUES ('aaa',100,'1998-01-01'); 45INSERT INTO t1 VALUES ('aaa',200,'1998-01-03'); 46INSERT INTO t1 VALUES ('bbb',50,'1998-01-02'); 47INSERT INTO t1 VALUES ('bbb',200,'1998-01-04'); 48select max(rdate) as s from t1 where rdate < '1998-01-03' having s> "1998-01-01"; 49s 501998-01-02 51select max(rdate) as s from t1 having s="1998-01-04"; 52s 531998-01-04 54select max(rdate+0) as s from t1 having s="19980104"; 55s 5619980104 57drop table t1; 58create table t1 (date date); 59insert into t1 values ("2000-08-10"),("2000-08-11"); 60select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1; 61date_add(date,INTERVAL 1 DAY) date_add(date,INTERVAL 1 SECOND) 622000-08-11 2000-08-10 00:00:01 632000-08-12 2000-08-11 00:00:01 64drop table t1; 65CREATE TABLE t1(AFIELD INT); 66INSERT INTO t1 VALUES(1); 67CREATE TABLE t2(GMT VARCHAR(32)); 68INSERT INTO t2 VALUES('GMT-0800'); 69SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) FROM t1, t2 GROUP BY t1.AFIELD; 70DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) 71Wed, 06 March 2002 10:11:12 GMT-0800 72INSERT INTO t1 VALUES(1); 73SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)), DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) FROM t1,t2 GROUP BY t1.AFIELD; 74DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) 75Wed, 06 March 2002 10:11:12 GMT-0800 Wed, 06 March 2002 10:11:12 GMT-0800 76drop table t1,t2; 77CREATE TABLE t1 (f1 time default NULL, f2 time default NULL); 78INSERT INTO t1 (f1, f2) VALUES ('09:00', '12:00'); 79SELECT DATE_FORMAT(f1, "%l.%i %p") , DATE_FORMAT(f2, "%l.%i %p") FROM t1; 80DATE_FORMAT(f1, "%l.%i %p") DATE_FORMAT(f2, "%l.%i %p") 819.00 AM 12.00 PM 82DROP TABLE t1; 83CREATE TABLE t1 (f1 DATE); 84CREATE TABLE t2 (f2 VARCHAR(8)); 85CREATE TABLE t3 (f2 CHAR(8)); 86INSERT INTO t1 VALUES ('1978-11-26'); 87INSERT INTO t2 SELECT f1+0 FROM t1; 88INSERT INTO t2 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1; 89INSERT INTO t3 SELECT f1+0 FROM t1; 90INSERT INTO t3 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1; 91SELECT * FROM t2; 92f2 9319781126 9419781126 95SELECT * FROM t3; 96f2 9719781126 9819781126 99DROP TABLE t1, t2, t3; 100CREATE TABLE t1 (y YEAR); 101INSERT INTO t1 VALUES ('abc'); 102Warnings: 103Warning 1366 Incorrect integer value: 'abc' for column `test`.`t1`.`y` at row 1 104SELECT * FROM t1; 105y 1060000 107DROP TABLE t1; 108create table t1(start_date date, end_date date); 109insert into t1 values ('2000-01-01','2000-01-02'); 110select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date; 1111 1121 113drop table t1; 114select @d:=1111; 115@d:=1111 1161111 117select year(@d), month(@d), day(@d), cast(@d as date); 118year(@d) month(@d) day(@d) cast(@d as date) 1192000 11 11 2000-11-11 120select @d:=011111; 121@d:=011111 12211111 123select year(@d), month(@d), day(@d), cast(@d as date); 124year(@d) month(@d) day(@d) cast(@d as date) 1252001 11 11 2001-11-11 126select @d:=1311; 127@d:=1311 1281311 129select year(@d), month(@d), day(@d), cast(@d as date); 130year(@d) month(@d) day(@d) cast(@d as date) 131NULL NULL NULL NULL 132Warnings: 133Warning 1292 Incorrect datetime value: '1311' 134Warning 1292 Incorrect datetime value: '1311' 135Warning 1292 Incorrect datetime value: '1311' 136Warning 1292 Incorrect datetime value: '1311' 137create table t1 (d date , dt datetime , ts timestamp); 138insert into t1 values (9912101,9912101,9912101); 139Warnings: 140Warning 1265 Data truncated for column 'd' at row 1 141Warning 1265 Data truncated for column 'dt' at row 1 142Warning 1265 Data truncated for column 'ts' at row 1 143insert into t1 values (11111,11111,11111); 144select * from t1; 145d dt ts 1460000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 1472001-11-11 2001-11-11 00:00:00 2001-11-11 00:00:00 148drop table t1; 149CREATE TABLE t1 ( 150a INT 151); 152INSERT INTO t1 VALUES (1); 153INSERT INTO t1 VALUES (NULL); 154SELECT str_to_date( '', a ) FROM t1; 155str_to_date( '', a ) 1560000-00-00 00:00:00.000000 157NULL 158DROP TABLE t1; 159CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); 160INSERT INTO t1 VALUES (DATE(NOW()), 1); 161SELECT COUNT(*) FROM t1 WHERE a = NOW(); 162COUNT(*) 1630 164EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); 165id select_type table type possible_keys key key_len ref rows Extra 1661 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 167INSERT INTO t1 VALUES (DATE(NOW()), 2); 168SELECT COUNT(*) FROM t1 WHERE a = NOW(); 169COUNT(*) 1700 171EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); 172id select_type table type possible_keys key key_len ref rows Extra 1731 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 174SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; 175COUNT(*) 1760 177EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; 178id select_type table type possible_keys key key_len ref rows Extra 1791 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 180ALTER TABLE t1 DROP PRIMARY KEY; 181SELECT COUNT(*) FROM t1 WHERE a = NOW(); 182COUNT(*) 1830 184EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); 185id select_type table type possible_keys key key_len ref rows Extra 1861 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 187DROP TABLE t1; 188CREATE TABLE t1 (a DATE); 189CREATE TABLE t2 (a DATE); 190CREATE INDEX i ON t1 (a); 191INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00'); 192INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00'); 193SELECT * FROM t1 WHERE a = '0000-00-00'; 194a 1950000-00-00 1960000-00-00 197SELECT * FROM t2 WHERE a = '0000-00-00'; 198a 1990000-00-00 2000000-00-00 201SET SQL_MODE=TRADITIONAL; 202EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00'; 203id select_type table type possible_keys key key_len ref rows Extra 2041 SIMPLE t1 ref i i 4 const 2 Using index 205SELECT * FROM t1 WHERE a = '0000-00-00'; 206a 2070000-00-00 2080000-00-00 209SELECT * FROM t2 WHERE a = '0000-00-00'; 210a 2110000-00-00 2120000-00-00 213INSERT INTO t1 VALUES ('0000-00-00'); 214ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`a` at row 1 215SET SQL_MODE=DEFAULT; 216DROP TABLE t1,t2; 217CREATE TABLE t1 (a DATE); 218CREATE TABLE t2 (a DATE); 219CREATE INDEX i ON t1 (a); 220INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00'); 221INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00'); 222SELECT * FROM t1 WHERE a = '1000-00-00'; 223a 2241000-00-00 2251000-00-00 226SELECT * FROM t2 WHERE a = '1000-00-00'; 227a 2281000-00-00 2291000-00-00 230SET SQL_MODE=TRADITIONAL; 231EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00'; 232id select_type table type possible_keys key key_len ref rows Extra 2331 SIMPLE t1 ref i i 4 const 2 Using index 234SELECT * FROM t1 WHERE a = '1000-00-00'; 235a 2361000-00-00 2371000-00-00 238SELECT * FROM t2 WHERE a = '1000-00-00'; 239a 2401000-00-00 2411000-00-00 242INSERT INTO t1 VALUES ('1000-00-00'); 243ERROR 22007: Incorrect date value: '1000-00-00' for column `test`.`t1`.`a` at row 1 244SET SQL_MODE=DEFAULT; 245DROP TABLE t1,t2; 246CREATE TABLE t1 SELECT curdate() AS f1; 247SELECT hour(f1), minute(f1), second(f1) FROM t1; 248hour(f1) minute(f1) second(f1) 2490 0 0 250DROP TABLE t1; 251End of 5.0 tests 252create table t1 (a date, primary key (a))engine=memory; 253insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01'); 254select * from t1 where a between '0000-00-01' and '0000-00-02'; 255a 2560000-00-01 257drop table t1; 258End of 5.1 tests 259