/*
日期类型的数据记录,通过like查询。在where tDate like '%a%'也能查询到记录
很奇怪,弱弱的问一下为什么?忘不吝赐教,多谢!!!
*/
if (exists (select name from sysobjects where name = 't'))
drop table t
go
create table t (
tId int primary key identity(1,1),
tDate datetime
)
go
insert into t values(getDate());--tDate like '%a%'查不到
insert into t values('2001-11-13');
insert into t values('2001-07-08');
insert into t values('2010-10-10');
insert into t values('2001-11-12');
insert into t values('2001-07-08');
insert into t values('2000-10-15');
insert into t values('2011-9-22');
insert into t values('2010-07-8');
insert into t values(getDate());--tDate like '%a%'查不到
insert into t values('2011-9-22 10:55:22');
insert into t values('2010-07-8 22:23:21');--tDate like '%a%'查不到select * from t where tDate like '%a%';select * from t where tid not in (select tId from t where tDate like '%a%');
你插入的时候是当天的时间
drop table t
go
create table t (
tId int primary key identity(1,1),
tDate datetime
)
go
insert into t values(getDate());--tDate like '%a%'查不到
insert into t values('2001-11-13');
insert into t values('2001-07-08');
insert into t values('2010-10-10');
insert into t values('2001-11-12');
insert into t values('2001-07-08');
insert into t values('2000-10-15');
insert into t values('2011-9-22');
insert into t values('2010-07-8');
insert into t values(getDate());--tDate like '%a%'查不到
insert into t values('2011-9-22 10:55:22');
insert into t values('2010-07-8 22:23:21');--tDate like '%a%'查不到 select * from ttId tDate
----------- -----------------------
1 2010-09-26 14:49:23.403
2 2001-11-13 00:00:00.000
3 2001-07-08 00:00:00.000
4 2010-10-10 00:00:00.000
5 2001-11-12 00:00:00.000
6 2001-07-08 00:00:00.000
7 2000-10-15 00:00:00.000
8 2011-09-22 00:00:00.000
9 2010-07-08 00:00:00.000
10 2010-09-26 14:49:23.483
11 2011-09-22 10:55:22.000
12 2010-07-08 22:23:21.000(12 行受影响)
按照你的说法,那这个字符串也能是的
insert into t values('2010-07-8 22:23:21');--tDate like '%a%'查不到
怎么不行
select出来的结果
如果你 select * from t where tDate like '%p%'就能查到另外三条了
为什么like '%a%'呢?
我试了下like '%b%' ,'%c%'等
你们再看看结果是什么呢? select * from t where tDate like '%C%';
select * from t where tid not in (select tId from t where tDate like '%C%');
tId tDate
----------- -----------------------
4 2010-10-10 00:00:00.000
7 2000-10-15 00:00:00.000(2 row(s) affected)tId tDate
----------- -----------------------
1 2010-09-26 14:58:51.947
2 2001-11-13 00:00:00.000
3 2001-07-08 00:00:00.000
5 2001-11-12 00:00:00.000
6 2001-07-08 00:00:00.000
8 2011-09-22 00:00:00.000
9 2010-07-08 00:00:00.000
10 2010-09-26 14:58:52.010
11 2011-09-22 10:55:22.000
12 2010-07-08 22:23:21.000(10 row(s) affected) select * from t where tDate like '%b%';
select * from t where tid not in (select tId from t where tDate like '%b%'); tId tDate
----------- -----------------------(0 row(s) affected)tId tDate
----------- -----------------------
1 2010-09-26 14:58:51.947
2 2001-11-13 00:00:00.000
3 2001-07-08 00:00:00.000
4 2010-10-10 00:00:00.000
5 2001-11-12 00:00:00.000
6 2001-07-08 00:00:00.000
7 2000-10-15 00:00:00.000
8 2011-09-22 00:00:00.000
9 2010-07-08 00:00:00.000
10 2010-09-26 14:58:52.010
11 2011-09-22 10:55:22.000
12 2010-07-08 22:23:21.000(12 row(s) affected)
就是这样的!09 26 2010 3:05PM
11 13 2001 12:00AM
07 8 2001 12:00AM
10 10 2010 12:00AM
11 12 2001 12:00AM
07 8 2001 12:00AM
10 15 2000 12:00AM
09 22 2011 12:00AM
07 8 2010 12:00AM
09 26 2010 3:05PM
09 22 2011 11:00AM
07 8 2010 10:23PM
drop table t
go
create table t (
tId int primary key identity(1,1),
tDate datetime
)
go
insert into t values(getDate());--tDate like '%a%'查不到
insert into t values('2001-11-13');
insert into t values('2001-07-08');
insert into t values('2010-10-10');
insert into t values('2001-11-12');
insert into t values('2001-07-08');
insert into t values('2000-10-15');
insert into t values('2011-9-22');
insert into t values('2010-07-8');
insert into t values(getDate());--tDate like '%a%'查不到
insert into t values('2011-9-22 10:55:22');
insert into t values('2010-07-8 22:23:21');--tDate like '%a%'查不到
--使用Convert将时间进行转换即可select convert(varchar(26),tDate,109) tDate from t where
convert(varchar(26),tDate,109) like '%a%'tDate
--------------------------
11 13 2001 12:00:00:000AM
07 8 2001 12:00:00:000AM
10 10 2010 12:00:00:000AM
11 12 2001 12:00:00:000AM
07 8 2001 12:00:00:000AM
10 15 2000 12:00:00:000AM
09 22 2011 12:00:00:000AM
07 8 2010 12:00:00:000AM
09 22 2011 10:55:22:000AM(9 行受影响)
你们太可爱了~~