create table t3(c1 int identity not null,c2 datetime) goinsert into t3(c2) values('2003-1-2 18:30:00') goinsert into t3(c2) values('2003-1-2') goinsert into t3(c2) values('18:30:00') goselect * from t3 where c2=convert(datetime,'2003-1-2 18:30:00') goselect * from t3 goc1 c2 ----------- ------------------------------------------------------ 1 2003-01-02 18:30:00.000 2 2003-01-02 00:00:00.000 3 1900-01-01 18:30:00.000所以,这三种情况要分开来处理 1 精准到日期和时间 select * from t3 where c2=convert(datetime,'2003-1-2 18:30:00') goc1 c2 ----------- ------------------------------------------------------ 1 2003-01-02 18:30:00.0002 查某天的 select * from t3 where c2 between convert(datetime,'2003-1-2') and dateadd(day,1,convert(datetime,'2003-1-2')) goc1 c2 ----------- ------------------------------------------------------ 1 2003-01-02 18:30:00.000 2 2003-01-02 00:00:00.0003 查某个时间点的 select * from t3 where datepart(Hour,c2)=datepart(hour,convert(datetime,'18:30')) and datepart(minute,c2)=datepart(minute,convert(datetime,'18:30')) and datepart(second,c2)=datepart(second,convert(datetime,'18:30')) goc1 c2 ----------- ------------------------------------------------------ 1 2003-01-02 18:30:00.000 3 1900-01-01 18:30:00.000
试试吧 select * from t1 where convert(char(10),时间字段,108)=>value and convert(char(10),时间字段,108)<=value
搂主应该好好看看三种情况下,field_time 中到底存放了是怎样的数据
goinsert into t3(c2) values('2003-1-2 18:30:00')
goinsert into t3(c2) values('2003-1-2')
goinsert into t3(c2) values('18:30:00')
goselect * from t3 where c2=convert(datetime,'2003-1-2 18:30:00')
goselect * from t3
goc1 c2
----------- ------------------------------------------------------
1 2003-01-02 18:30:00.000
2 2003-01-02 00:00:00.000
3 1900-01-01 18:30:00.000所以,这三种情况要分开来处理
1 精准到日期和时间
select * from t3 where c2=convert(datetime,'2003-1-2 18:30:00')
goc1 c2
----------- ------------------------------------------------------
1 2003-01-02 18:30:00.0002 查某天的
select * from t3 where c2 between convert(datetime,'2003-1-2')
and dateadd(day,1,convert(datetime,'2003-1-2'))
goc1 c2
----------- ------------------------------------------------------
1 2003-01-02 18:30:00.000
2 2003-01-02 00:00:00.0003 查某个时间点的
select * from t3
where datepart(Hour,c2)=datepart(hour,convert(datetime,'18:30'))
and datepart(minute,c2)=datepart(minute,convert(datetime,'18:30'))
and datepart(second,c2)=datepart(second,convert(datetime,'18:30'))
goc1 c2
----------- ------------------------------------------------------
1 2003-01-02 18:30:00.000
3 1900-01-01 18:30:00.000
1、我需要写一个通用查询
2、field_time只是一个示意字段
3、field_time(datetime类型)在SQL Server中
可以存储以上所述三种情况的时间格式,
各位可以自己去SQL Server中试一下,
而且我并不能在查找之前判断field_time
存储的是哪种情况的时间格式。
4、通用查询中不仅包含“=”的情况,
还有“>=”和“<=”的情况。如果有哪位同仁曾经成功地做过这方面的查询,
不妨共享。再发帖送分!感激涕零!