type_datetime.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 432 行 · 第 1/2 页

Note	1265	Data truncated for column 'da' at row 1
insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
ERROR 22007: Incorrect date value: '2007-03-32' for column 'da' at row 1
select * from t1;
da	dt
1962-03-03	1962-03-03 00:00:00
2007-03-23	2007-03-23 13:49:38
2007-03-23	2007-03-23 13:49:38
2007-03-23	2007-03-23 13:49:38
drop table t1;
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
ERROR 42000: Invalid default value for 'da'
create table t1 (t time default '916:00:00 a');
ERROR 42000: Invalid default value for 't'
set @@sql_mode= @org_mode;
create table t1 (f1 date, f2 datetime, f3 timestamp);
insert into t1(f1) values(curdate());
select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
curdate() < now()	f1 < now()	cast(f1 as date) < now()
1	1	1
delete from t1;
insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
f1	f3
2001-02-05	2001-02-05 01:01:01
2001-03-10	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00
select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
f1	f3
2001-02-05	2001-02-05 01:01:01
2001-03-10	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00
select f1, f2 from t1 where if(1, f1, 0) >= f2;
f1	f2
2001-02-05	2001-02-05 00:00:00
2001-03-10	2001-03-09 01:01:01
2001-04-15	2001-04-15 00:00:00
select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
1
1
select f1, f2, f1 > f2, f1 = f2, f1 < f2 from t1;
f1	f2	f1 > f2	f1 = f2	f1 < f2
2001-01-01	2001-01-01 01:01:01	0	0	1
2001-02-05	2001-02-05 00:00:00	0	1	0
2001-03-10	2001-03-09 01:01:01	1	0	0
2001-04-15	2001-04-15 00:00:00	0	1	0
2001-05-20	2001-05-20 01:01:01	0	0	1
drop table t1;
create table t1 (f1 date, f2 datetime, f3 timestamp);
insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
f2
2001-02-05 00:00:00
2001-03-09 01:01:01
select f1, f2, f3 from t1 where f1 between f2 and f3;
f1	f2	f3
2001-02-05	2001-02-05 00:00:00	2001-02-05 01:01:01
2001-03-10	2001-03-09 01:01:01	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00	2001-04-15 00:00:00
select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
cast(f3 as date);
f1	f2	f3
2001-02-05	2001-02-05 00:00:00	2001-02-05 01:01:01
2001-03-10	2001-03-09 01:01:01	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00	2001-04-15 00:00:00
select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
f2
2001-01-01 01:01:01
2001-02-05 00:00:00
2001-03-09 01:01:01
select f2, f3 from t1 where '01-03-10' between f2 and f3;
f2	f3
2001-03-09 01:01:01	2001-03-10 01:01:01
select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
f2
2001-04-15 00:00:00
SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
1
drop table t1;
create table t1 (f1 date);
insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');
f1
2001-01-01
2001-01-02
2001-01-03
create table t2(f2 datetime);
insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33');
select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03');
f2
2001-01-01 00:00:00
2001-02-03 12:34:56
select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date));
f1	f2
2001-01-02	2001-01-01 00:00:00
2001-01-02	2001-02-03 12:34:56
2001-01-02	2002-04-06 11:22:33
select * from t1,t2 where '01-01-01' in (f1, '01-02-03');
f1	f2
2001-01-01	2001-01-01 00:00:00
2001-01-01	2001-02-03 12:34:56
2001-01-01	2002-04-06 11:22:33
select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2);
f1	f2
2001-01-01	2001-02-03 12:34:56
2001-01-02	2001-02-03 12:34:56
2001-01-03	2001-02-03 12:34:56
create table t3(f3 varchar(20));
insert into t3 select * from t2;
select * from t2,t3 where f2 in (f3,'03-04-05');
f2	f3
2001-01-01 00:00:00	2001-01-01 00:00:00
2001-02-03 12:34:56	2001-02-03 12:34:56
2002-04-06 11:22:33	2002-04-06 11:22:33
select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1'));
f1	f2	f3
2001-01-01	2001-01-01 00:00:00	2001-01-01 00:00:00
2001-01-01	2001-02-03 12:34:56	2001-01-01 00:00:00
2001-01-01	2002-04-06 11:22:33	2001-01-01 00:00:00
2001-01-01	2001-01-01 00:00:00	2001-02-03 12:34:56
2001-01-01	2001-01-01 00:00:00	2002-04-06 11:22:33
select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02'));
f1
2001-01-01
drop table t1,t2,t3;
select least(cast('01-01-01' as date), '01-01-02');
least(cast('01-01-01' as date), '01-01-02')
2001-01-01
select greatest(cast('01-01-01' as date), '01-01-02');
greatest(cast('01-01-01' as date), '01-01-02')
01-01-02
select least(cast('01-01-01' as date), '01-01-02') + 0;
least(cast('01-01-01' as date), '01-01-02') + 0
20010101
select greatest(cast('01-01-01' as date), '01-01-02') + 0;
greatest(cast('01-01-01' as date), '01-01-02') + 0
20010102
select least(cast('01-01-01' as datetime), '01-01-02') + 0;
least(cast('01-01-01' as datetime), '01-01-02') + 0
20010101000000
select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);
cast(least(cast('01-01-01' as datetime), '01-01-02') as signed)
20010101000000
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2));
cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(20,2))
20010101000000.00
DROP PROCEDURE IF EXISTS test27759 ;
CREATE PROCEDURE test27759()
BEGIN
declare v_a date default '2007-4-10';
declare v_b date default '2007-4-11';
declare v_c datetime default '2004-4-9 0:0:0';
select v_a as a,v_b as b,
least( v_a, v_b ) as a_then_b,
least( v_b, v_a ) as b_then_a,
least( v_c, v_a ) as c_then_a;
END;|
call test27759();
a	b	a_then_b	b_then_a	c_then_a
2007-04-10	2007-04-11	2007-04-10	2007-04-10	2004-04-09 00:00:00
drop procedure test27759;
create table t1 (f1 date);
insert into t1 values (curdate());
select left(f1,10) = curdate() from t1;
left(f1,10) = curdate()
1
drop table t1;
create table t1(f1 date);
insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
set @bug28261='';
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
if(@bug28261 = f1, '', @bug28261:= f1)
2001-01-01
2002-02-02
2001-01-01
2002-02-02
Warnings:
Warning	1292	Incorrect date value: '' for column 'f1' at row 1
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
if(@bug28261 = f1, '', @bug28261:= f1)
2001-01-01
2002-02-02
2001-01-01
2002-02-02
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
if(@bug28261 = f1, '', @bug28261:= f1)
2001-01-01
2002-02-02
2001-01-01
2002-02-02
drop table t1;
create table t1(f1 datetime);
insert into t1 values('2001-01-01'),('2002-02-02');
select * from t1 where f1 between 20020101 and 20070101000000;
f1
2002-02-02 00:00:00
select * from t1 where f1 between 2002010 and 20070101000000;
f1
2001-01-01 00:00:00
2002-02-02 00:00:00
Warnings:
Warning	1292	Incorrect datetime value: '2002010' for column 'f1' at row 1
select * from t1 where f1 between 20020101 and 2007010100000;
f1
Warnings:
Warning	1292	Incorrect datetime value: '2007010100000' for column 'f1' at row 1
drop table t1;