2
# test of problem with date fields
5
drop table if exists t1,t2;
8
create table t1 (a char(16), b date, c datetime);
9
insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01';
10
select * from t1 where c = '2000-01-01';
11
select * from t1 where b = '2000-01-01';
15
# problem with date conversions
18
CREATE TABLE t1 (name char(6),cdate date);
19
INSERT INTO t1 VALUES ('name1','1998-01-01');
20
INSERT INTO t1 VALUES ('name2','1998-01-01');
21
INSERT INTO t1 VALUES ('name1','1998-01-02');
22
INSERT INTO t1 VALUES ('name2','1998-01-02');
23
CREATE TABLE t2 (cdate date, note char(6));
24
INSERT INTO t2 VALUES ('1998-01-01','note01');
25
INSERT INTO t2 VALUES ('1998-01-02','note02');
26
select name,t1.cdate,note from t1,t2 where t1.cdate=t2.cdate and t1.cdate='1998-01-01';
33
CREATE TABLE t1 ( datum DATE );
34
INSERT INTO t1 VALUES ( "2000-1-1" );
35
INSERT INTO t1 VALUES ( "2000-1-2" );
36
INSERT INTO t1 VALUES ( "2000-1-3" );
37
INSERT INTO t1 VALUES ( "2000-1-4" );
38
INSERT INTO t1 VALUES ( "2000-1-5" );
39
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date);
40
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY;
44
# test of max(date) and having
52
INSERT INTO t1 VALUES ('aaa',100,'1998-01-01');
53
INSERT INTO t1 VALUES ('aaa',200,'1998-01-03');
54
INSERT INTO t1 VALUES ('bbb',50,'1998-01-02');
55
INSERT INTO t1 VALUES ('bbb',200,'1998-01-04');
56
select max(rdate) as s from t1 where rdate < '1998-01-03' having s> "1998-01-01";
57
select max(rdate) as s from t1 having s="1998-01-04";
58
select max(rdate+0) as s from t1 having s="19980104";
62
# Test of date and not null
65
create table t1 (date date);
66
insert into t1 values ("2000-08-10"),("2000-08-11");
67
select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1;
71
# Test problem with DATE_FORMAT
74
CREATE TABLE t1(AFIELD INT);
75
INSERT INTO t1 VALUES(1);
76
CREATE TABLE t2(GMT VARCHAR(32));
77
INSERT INTO t2 VALUES('GMT-0800');
78
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;
79
INSERT INTO t1 VALUES(1);
80
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;
84
# Multiple SELECT DATE_FORMAT gave incorrect results (Bug #4036)
87
CREATE TABLE t1 (f1 time default NULL, f2 time default NULL);
88
INSERT INTO t1 (f1, f2) VALUES ('09:00', '12:00');
89
SELECT DATE_FORMAT(f1, "%l.%i %p") , DATE_FORMAT(f2, "%l.%i %p") FROM t1;
93
# Bug 4937: different date -> string conversion when using SELECT ... UNION
94
# and INSERT ... SELECT ... UNION
97
CREATE TABLE t1 (f1 DATE);
98
CREATE TABLE t2 (f2 VARCHAR(8));
99
CREATE TABLE t3 (f2 CHAR(8));
101
INSERT INTO t1 VALUES ('1978-11-26');
102
INSERT INTO t2 SELECT f1+0 FROM t1;
103
INSERT INTO t2 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
104
INSERT INTO t3 SELECT f1+0 FROM t1;
105
INSERT INTO t3 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
109
DROP TABLE t1, t2, t3;
111
# Test that setting YEAR to invalid string results in default value, not
113
CREATE TABLE t1 (y YEAR);
114
INSERT INTO t1 VALUES ('abc');
119
# Bug#21677: Wrong result when comparing a DATE and a DATETIME in BETWEEN
121
create table t1(start_date date, end_date date);
122
insert into t1 values ('2000-01-01','2000-01-02');
123
select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date;