数据库中的一张表t, t(tickno char(10),class_date datetime), 现在连续的几行记录如:
1,2010-10-13 00:00:00.000
2,2010-10-13 00:00:00.000
3,2010-10-13 00:00:00.000
4,2010-10-13 00:00:00.000
5,2010-10-13 00:00:00.000现在的问题是1,4,5可以通过时间查询出来,但中间两条通过时间就是查不出来
select * from t where class_date='2010-10-13 00:00:00.000'
这条语句只查的出前后的,中间的就是查不出来。为何呢?
即使用
select * from t where class_date between '2010-10-12 00:00:01.000' and '2010-10-14 23:59:59.000'
这条也查询不出来,搞不懂了,难道是bug? 用datepart分别取各值出来的解是2010-10-13!
1,2010-10-13 00:00:00.000
2,2010-10-13 00:00:00.000
3,2010-10-13 00:00:00.000
4,2010-10-13 00:00:00.000
5,2010-10-13 00:00:00.000现在的问题是1,4,5可以通过时间查询出来,但中间两条通过时间就是查不出来
select * from t where class_date='2010-10-13 00:00:00.000'
这条语句只查的出前后的,中间的就是查不出来。为何呢?
即使用
select * from t where class_date between '2010-10-12 00:00:01.000' and '2010-10-14 23:59:59.000'
这条也查询不出来,搞不懂了,难道是bug? 用datepart分别取各值出来的解是2010-10-13!
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[date] datetime)
insert [tb]
select 1,'2010-10-13 00:00:00.000' union all
select 2,'2010-10-13 00:00:00.000' union all
select 3,'2010-10-13 00:00:00.000' union all
select 4,'2010-10-13 00:00:00.000' union all
select 5,'2010-10-13 00:00:00.000'
go-->版本
/*
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
(1 行受影响)*/
select * from [tb] where [date]='2010-10-13 00:00:00.000'
/*
id date
----------- -----------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000(5 行受影响)
*/
select datepart(day,Class_Date),datepart(MONTH,Class_Date),datepart(YEAR,Class_Date),
datepart(HOUR,Class_Date),datepart(MINUTE,Class_Date),datepart(SECOND,Class_Date),
datepart(ss,Class_Date)
from t
where tickno =2
出来全部是0
from t
where Tickno=2------------------
2010-10-13 00:00:00
if object_id('tempdb.dbo.#') is not null drop table #
create table #(tickno int, class_date datetime)
insert into #
select 1, '2010-10-13 00:00:00.000' union all
select 2, '2010-10-13 00:00:00.000' union all
select 3, '2010-10-13 00:00:00.000' union all
select 4, '2010-10-13 00:00:00.000' union all
select 5, '2010-10-13 00:00:00.000'select @@version
/*
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )
*/select * from # where class_date='2010-10-13 00:00:00.000'/*
tickno class_date
----------- -----------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000
*/
结果为0, 已经检查过了.
并且用between '前一天' and '后一天' 都查询不到
还有其它where 条件吗?
真实的表里还有另外一个时间字段toll_dateTime
select * from T
where toll_dateTime='2010-10-13 14:17:51.000'
and Class_Date='2010-10-13 00:00:00.000'
and TickNO=2
这一句可以查询出来,但是
select * from T
where Class_Date='2010-10-13 00:00:00.000'
and TickNO=2
这样去年第一个条件就查询不出来了.
为何呢?
where Class_Date='2010-10-13 00:00:00.000'
and TickNO=2
====
select * from T
where convert(varchar(10),Class_Date,120)='2010-10-13'
and TickNO=2看看有没有结果。
insert [tb]
select 1,'2010-10-13 00:00:00.000' union all
select 2,'2010-10-13 00:00:00.000' union all
select 3,'2010-10-13 00:00:00.000' union all
select 4,'2010-10-13 00:00:00.000' union all
select 5,'2010-10-13 00:00:00.000'
goselect * from tb where date = '2010-10-13 00:00:00.000'select * from tb where convert(varchar(10),date,120) = '2010-10-13'select * from tb where datediff(day,date, '2010-10-13') = 0/*
id date
----------- ------------------------------------------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000(所影响的行数为 5 行)id date
----------- ------------------------------------------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000(所影响的行数为 5 行)id date
----------- ------------------------------------------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000(所影响的行数为 5 行)*/drop table tb
确定你的表里字段tickno 类型是char(10)??class_date 类型是 datetime??
如果是char(10)
貌似你给的数据不对1,2010-10-13 00:00:00.000
2,2010-10-13 00:00:00.000
3,2010-10-13 00:00:00.000
4,2010-10-13 00:00:00.000
5,2010-10-13 00:00:00.000
应该是自动补空格到10个字符你可以在出问题的sql服务器按你说的条件建个表 查查试试 排除真是sql bug再说
select * from tb where date = '2010-10-13 00:00:00.000'select * from tb where convert(varchar(10),date,120) = '2010-10-13'select * from tb where datediff(day,date, '2010-10-13') = 0前两种一样的查询不出来,后一种可以查询出,点解?
你的字符也许存在问题,例如不是标准的ANSI编码.
你可以尝试把所有的date导出来到文本中检查一下.或者:
select * from tb where date = N'2010-10-13 00:00:00.000'select * from tb where convert(varchar(10),date,120) = N'2010-10-13'select * from tb where datediff(day,date, N'2010-10-13') = 0
结果一样,还是只有采用datediff的可以查询出来
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
(1 行受影响)id date
----------- -----------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000(5 行受影响)id date
----------- -----------------------
1 2010-10-13 00:00:00.000
2 2010-10-13 00:00:00.000
3 2010-10-13 00:00:00.000
4 2010-10-13 00:00:00.000
5 2010-10-13 00:00:00.000(5 行受影响)没有问题。。
如果是这样的话,你在开个贴给我100
哥们,classDate肯定不是varchar类型的
如全角与半角,ansii与unicode区别。
或者前面,后面含 有 tab,空格,等。
ascii()
declare @i int
set @i=1
declare @s varchar(20)
select @s=select top 1 convert(varchar(20),'2010-10-1') from tb where TickNO=2
while @i<20
begin
select ascii(substring(@s,@i,1)) from tb where TickNO=2
set @i=@i+1
end