left(cast(timeid as varchar),8)='2005-5-9' and right(cast(timeid as varchar),5)='00:00'
TO_CHAR(timeid,'YYYY-MM-DD HH24')?
--显示查询2005-5-9这天24个小时整点的24条内容 left(cast(timeid as varchar),8)='2005-5-9' and right(cast(timeid as varchar),5)='00:00'--显示2005-5-9这个月每天12点的内容 left(cast(timeid as varchar),6)='2005-5' and right(cast(timeid as varchar),8)='12:00:00'
create table #t ( TimeId smalldatetime )insert #t select '2005-05-09 00:12:34' union all select '2005-05-09 20:00:00' union all select '2005-05-09 20:11:00' union all select '2005-05-09 23:00:00' union all select '2005-05-10 00:00:00' union all select '2005-05-10 12:00:00' union all select '2005-06-01 11:18:00'select TimeId from #t where datepart(mi,TimeId)=0 and datepart(ss,TimeId)=0 and convert(varchar,TimeId,101)='05/09/2005'---结果: /* TimeID 2005-05-09 20:00:00 2005-05-09 23:00:00 */select TimeId from #t where datepart(hh,TimeId)=12 and datepart(mi,TimeId)=0 and datepart(ss,TimeId)=0 and datepart(mm,TimeId)=5---结果: /* TimeID 2005-05-10 12:00:00 */drop table #t可以根据楼主的需求改动。。看楼下
我喜欢用datediff()函数.就是已0点为计算的。
这接where timeid 后面吗?
直接放在where 后面就可以了
--显示查询2005-5-9这天24个小时整点的24条内容 where left(cast(timeid as varchar),8)='2005-5-9' and right(cast(timeid as varchar),5)='00:00' --或者 where timeid between '2005-5-9 00:00:00' and '2005-5-9 23:59:59' and datepart(minute,timeid)=0 and datepart(second,timeid)=0 --或者(sql server2008) where cast(timeid as date)='2005-05-09' and datepart(minute,timeid)=0 and datepart(second,timeid)=0 --显示2005-5-9这个月每天12点的内容 where left(cast(timeid as varchar),6)='2005-5' and right(cast(timeid as varchar),8)='12:00:00' --或者类似上面的变换
求救啊,jeansy911大大 和fpzgm大大,你们SQL 语句都很好,但是我运用到我的数据库查询为什么查不出来。 我分别用了 select timeid from sample_m5_000 where left(cast(timeid as varchar),8)='2008-8-2' and right(cast(timeid as varchar),5)='00:00' 和 select TimeId from sample_m5_000 where datepart(mi,TimeId)=0 and datepart(ss,TimeId)=0 and convert(varchar,TimeId,101)='02/08/2008' 但是都没结果!
--多一个循环 select pointname,timeid from m5define, sample_m5_000,master..spt_values m where m.type='p' and m.number between 1 and 24 and id=90 and timeid =dateadd(hour,m.number,'2005-5-9 00:00:00')
left(cast(timeid as varchar),8)='2005-5-9' and right(cast(timeid as varchar),5)='00:00'
--显示查询2005-5-9这天24个小时整点的24条内容
left(cast(timeid as varchar),8)='2005-5-9' and right(cast(timeid as varchar),5)='00:00'--显示2005-5-9这个月每天12点的内容
left(cast(timeid as varchar),6)='2005-5' and right(cast(timeid as varchar),8)='12:00:00'
create table #t
(
TimeId smalldatetime
)insert #t
select '2005-05-09 00:12:34'
union all
select '2005-05-09 20:00:00'
union all
select '2005-05-09 20:11:00'
union all
select '2005-05-09 23:00:00'
union all
select '2005-05-10 00:00:00'
union all
select '2005-05-10 12:00:00'
union all
select '2005-06-01 11:18:00'select TimeId from #t
where datepart(mi,TimeId)=0 and
datepart(ss,TimeId)=0 and
convert(varchar,TimeId,101)='05/09/2005'---结果:
/*
TimeID
2005-05-09 20:00:00
2005-05-09 23:00:00
*/select TimeId from #t
where
datepart(hh,TimeId)=12 and
datepart(mi,TimeId)=0 and
datepart(ss,TimeId)=0 and
datepart(mm,TimeId)=5---结果:
/*
TimeID
2005-05-10 12:00:00
*/drop table #t可以根据楼主的需求改动。。看楼下
--显示查询2005-5-9这天24个小时整点的24条内容
where left(cast(timeid as varchar),8)='2005-5-9' and right(cast(timeid as varchar),5)='00:00'
--或者
where timeid between '2005-5-9 00:00:00' and '2005-5-9 23:59:59'
and datepart(minute,timeid)=0 and datepart(second,timeid)=0
--或者(sql server2008)
where cast(timeid as date)='2005-05-09'
and datepart(minute,timeid)=0 and datepart(second,timeid)=0
--显示2005-5-9这个月每天12点的内容
where left(cast(timeid as varchar),6)='2005-5' and right(cast(timeid as varchar),8)='12:00:00'
--或者类似上面的变换
我分别用了
select timeid from sample_m5_000
where left(cast(timeid as varchar),8)='2008-8-2'
and right(cast(timeid as varchar),5)='00:00'
和
select TimeId from sample_m5_000
where datepart(mi,TimeId)=0
and datepart(ss,TimeId)=0
and convert(varchar,TimeId,101)='02/08/2008'
但是都没结果!
--多一个循环
select pointname,timeid from m5define, sample_m5_000,master..spt_values m where m.type='p' and m.number between 1 and 24 and id=90 and timeid =dateadd(hour,m.number,'2005-5-9 00:00:00')