查询昨天下午16:00-18:00的数据

[zhanxiangma]

shijian (字段名称，类型为char(20)类型)
2011-1-4 10:52:31

现在我要一条通用的查询昨天下午16:00-18:00的数据，怎么写呢？

[Shawn]

SELECT * FROM table_name
WHERE CAST(shijian AS DATETIME) >= (CAST(DATEADD(DAY, -1, GETDATE()) AS VARCHAR(10)) + ' 16:00:00')
  AND CAST(shijian AS DATETIME) <= (CAST(DATEADD(DAY, -1, GETDATE()) AS VARCHAR(10)) + ' 18:00:00')

[Rotel-刘志东]

-- 查询昨天下午16:00-18:00的数据
select *
from tb 
where time >= convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 16:00:00'
and time < convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 18:00:

00'

[Rotel-刘志东]

---- 查询昨天下午16:00-18:00的数据
select * from tb
where time >= convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 16:00:00'
and time < convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 18:00:00'

[zhanxiangma]

shijian已是varchar类型了，给位的sql 都不能执行啊？？？那位仁兄有更好的呢？ 我主要是想实现 “查询昨天16-18点的数据（与系统的当天时间作对比）”

[快溜]

昨天是哪天?

[快溜]

楼上的sql都给力呢。

[gogodiy]

where convert(datetime,shijian)>=convert(datetime,convert(varchar(10),convert(datetime,shijian),120)+' 16:00:00') 
and convert(datetime,shijian)<=convert(datetime,convert(varchar(10),convert(datetime,shijian),120)+' 18:00:00')

[华夏小卒]

select * from tb
where shijian between convert(varchar(10),(dateadd(day,-1,getdate()),120)+' 16:00:00' and convert(varchar(10),(dateadd(day,-1,getdate()),120)+' 18:00:00' 

[sql_bd]

时间最好用datetime类型

datediff(dd,shijian,getdate()-1)=0 and convert(varchar(5),shijian,108) between '16:00' and '18:00'

[昵称被占用了]

select *
from tab
where time >= convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 16:00:00'
and time < convert(varchar(10),dateadd(day,-1,getdate()),120) + ' 18:00:00'

[飘零一叶]

select * from tb
where shijian between convert(varchar(10),(dateadd(day,-1,getdate()),120)+' 16:00:00' and convert(varchar(10),(dateadd(day,-1,getdate()),120)+' 18:00:00'

[ywspace]

[Quote=引用 1 楼 js_szy 的回复:]
SQL code
select * from tb
where shijian between convert(varchar(10),(dateadd(day,-1,getdate()),120)+' 16:00:00' and convert(varchar(10),(dateadd(day,-1,getdate()),120)+' 18:00:00'
[/Quote]


顶

[zhanxiangma]

SELECT id,name,shijian FROM temp where
(substring(convert(VARCHAR(20),convert(datetime,shijian),120),1,charindex(' ',shijian) )
= substring( convert(varchar(20), getdate()-2,120) ,1,charindex(' ',shijian))
and substring(shijian,charindex(' ',shijian)+1, len( shijian)- charindex(' ',shijian)) between '09:00' and '11:00' )

我的这个查出来的结果是：
1 11 2011-1-4 10:52:31
2 rr 2011-01-03 10:52:31
请问如何把 2011-1-4 10:52:31（数据表中的数据） 转换成 系统时间的格式