1
SET DEFAULT_STORAGE_ENGINE='tokudb';
2
drop table if exists t1,t2,t3;
3
create table t1 (a char(16), b date, c datetime);
4
insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01';
5
select * from t1 where c = '2000-01-01';
7
test 2000-01-01 2000-01-01 2000-01-01 00:00:00
8
select * from t1 where b = '2000-01-01';
10
test 2000-01-01 2000-01-01 2000-01-01 00:00:00
12
CREATE TABLE t1 (name char(6),cdate date);
13
INSERT INTO t1 VALUES ('name1','1998-01-01');
14
INSERT INTO t1 VALUES ('name2','1998-01-01');
15
INSERT INTO t1 VALUES ('name1','1998-01-02');
16
INSERT INTO t1 VALUES ('name2','1998-01-02');
17
CREATE TABLE t2 (cdate date, note char(6));
18
INSERT INTO t2 VALUES ('1998-01-01','note01');
19
INSERT INTO t2 VALUES ('1998-01-02','note02');
20
select name,t1.cdate,note from t1,t2 where t1.cdate=t2.cdate and t1.cdate='1998-01-01';
22
name1 1998-01-01 note01
23
name2 1998-01-01 note01
25
CREATE TABLE t1 ( datum DATE );
26
INSERT INTO t1 VALUES ( "2000-1-1" );
27
INSERT INTO t1 VALUES ( "2000-1-2" );
28
INSERT INTO t1 VALUES ( "2000-1-3" );
29
INSERT INTO t1 VALUES ( "2000-1-4" );
30
INSERT INTO t1 VALUES ( "2000-1-5" );
31
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date);
36
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY;
44
INSERT INTO t1 VALUES ('aaa',100,'1998-01-01');
45
INSERT INTO t1 VALUES ('aaa',200,'1998-01-03');
46
INSERT INTO t1 VALUES ('bbb',50,'1998-01-02');
47
INSERT INTO t1 VALUES ('bbb',200,'1998-01-04');
48
select max(rdate) as s from t1 where rdate < '1998-01-03' having s> "1998-01-01";
51
select max(rdate) as s from t1 having s="1998-01-04";
54
select max(rdate+0) as s from t1 having s="19980104";
58
create table t1 (date date);
59
insert into t1 values ("2000-08-10"),("2000-08-11");
60
select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1;
61
date_add(date,INTERVAL 1 DAY) date_add(date,INTERVAL 1 SECOND)
62
2000-08-11 2000-08-10 00:00:01
63
2000-08-12 2000-08-11 00:00:01
65
CREATE TABLE t1(AFIELD INT);
66
INSERT INTO t1 VALUES(1);
67
CREATE TABLE t2(GMT VARCHAR(32));
68
INSERT INTO t2 VALUES('GMT-0800');
69
SELECT 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;
70
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT))
71
Wed, 06 March 2002 10:11:12 GMT-0800
72
INSERT INTO t1 VALUES(1);
73
SELECT 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;
74
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))
75
Wed, 06 March 2002 10:11:12 GMT-0800 Wed, 06 March 2002 10:11:12 GMT-0800
77
CREATE TABLE t1 (f1 time default NULL, f2 time default NULL);
78
INSERT INTO t1 (f1, f2) VALUES ('09:00', '12:00');
79
SELECT DATE_FORMAT(f1, "%l.%i %p") , DATE_FORMAT(f2, "%l.%i %p") FROM t1;
80
DATE_FORMAT(f1, "%l.%i %p") DATE_FORMAT(f2, "%l.%i %p")
83
CREATE TABLE t1 (f1 DATE);
84
CREATE TABLE t2 (f2 VARCHAR(8));
85
CREATE TABLE t3 (f2 CHAR(8));
86
INSERT INTO t1 VALUES ('1978-11-26');
87
INSERT INTO t2 SELECT f1+0 FROM t1;
88
INSERT INTO t2 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
89
INSERT INTO t3 SELECT f1+0 FROM t1;
90
INSERT INTO t3 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
99
DROP TABLE t1, t2, t3;
100
CREATE TABLE t1 (y YEAR);
101
INSERT INTO t1 VALUES ('abc');
103
Warning 1366 Incorrect integer value: 'abc' for column 'y' at row 1
108
create table t1(start_date date, end_date date);
109
insert into t1 values ('2000-01-01','2000-01-02');
110
select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date;
117
select year(@d), month(@d), day(@d), cast(@d as date);
118
year(@d) month(@d) day(@d) cast(@d as date)
119
2000 11 11 2000-11-11
123
select year(@d), month(@d), day(@d), cast(@d as date);
124
year(@d) month(@d) day(@d) cast(@d as date)
125
2001 11 11 2001-11-11
129
select year(@d), month(@d), day(@d), cast(@d as date);
130
year(@d) month(@d) day(@d) cast(@d as date)
133
Warning 1292 Incorrect datetime value: '1311'
134
Warning 1292 Incorrect datetime value: '1311'
135
Warning 1292 Incorrect datetime value: '1311'
136
Warning 1292 Incorrect datetime value: '1311'
137
create table t1 (d date , dt datetime , ts timestamp);
138
insert into t1 values (9912101,9912101,9912101);
140
Warning 1265 Data truncated for column 'd' at row 1
141
Warning 1265 Data truncated for column 'dt' at row 1
142
Warning 1265 Data truncated for column 'ts' at row 1
143
insert into t1 values (11111,11111,11111);
146
0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00
147
2001-11-11 2001-11-11 00:00:00 2001-11-11 00:00:00
152
INSERT INTO t1 VALUES (1);
153
INSERT INTO t1 VALUES (NULL);
154
SELECT str_to_date( '', a ) FROM t1;
159
CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b));
160
INSERT INTO t1 VALUES (DATE(NOW()), 1);
161
SELECT COUNT(*) FROM t1 WHERE a = NOW();
164
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
165
id select_type table type possible_keys key key_len ref rows Extra
166
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
167
INSERT INTO t1 VALUES (DATE(NOW()), 2);
168
SELECT COUNT(*) FROM t1 WHERE a = NOW();
171
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
172
id select_type table type possible_keys key key_len ref rows Extra
173
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
174
SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
177
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
178
id select_type table type possible_keys key key_len ref rows Extra
179
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
180
ALTER TABLE t1 DROP PRIMARY KEY;
181
SELECT COUNT(*) FROM t1 WHERE a = NOW();
184
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
185
id select_type table type possible_keys key key_len ref rows Extra
186
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
188
CREATE TABLE t1 (a DATE);
189
CREATE TABLE t2 (a DATE);
190
CREATE INDEX i ON t1 (a);
191
INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00');
192
INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00');
193
SELECT * FROM t1 WHERE a = '0000-00-00';
197
SELECT * FROM t2 WHERE a = '0000-00-00';
201
SET SQL_MODE=TRADITIONAL;
202
EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00';
203
id select_type table type possible_keys key key_len ref rows Extra
204
1 SIMPLE t1 ref i i 4 const 2 Using index
205
SELECT * FROM t1 WHERE a = '0000-00-00';
209
SELECT * FROM t2 WHERE a = '0000-00-00';
213
INSERT INTO t1 VALUES ('0000-00-00');
214
ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
215
SET SQL_MODE=DEFAULT;
217
CREATE TABLE t1 (a DATE);
218
CREATE TABLE t2 (a DATE);
219
CREATE INDEX i ON t1 (a);
220
INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00');
221
INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00');
222
SELECT * FROM t1 WHERE a = '1000-00-00';
226
SELECT * FROM t2 WHERE a = '1000-00-00';
230
SET SQL_MODE=TRADITIONAL;
231
EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00';
232
id select_type table type possible_keys key key_len ref rows Extra
233
1 SIMPLE t1 ref i i 4 const 2 Using index
234
SELECT * FROM t1 WHERE a = '1000-00-00';
238
SELECT * FROM t2 WHERE a = '1000-00-00';
242
INSERT INTO t1 VALUES ('1000-00-00');
243
ERROR 22007: Incorrect date value: '1000-00-00' for column 'a' at row 1
244
SET SQL_MODE=DEFAULT;
246
CREATE TABLE t1 SELECT curdate() AS f1;
247
SELECT hour(f1), minute(f1), second(f1) FROM t1;
248
hour(f1) minute(f1) second(f1)
252
create table t1 (a date, primary key (a))engine=memory;
253
insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01');
254
select * from t1 where a between '0000-00-01' and '0000-00-02';