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'
时间最好用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'
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')
-- 查询昨天下午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'
---- 查询昨天下午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'
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 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(数据表中的数据) 转换成 系统时间的格式
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'
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'
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'
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')
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'
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'
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 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(数据表中的数据) 转换成 系统时间的格式