我想将数据库中的某个格式为"yyyy-mm-dd hh:mm:ss"的时间字段的值,其中的时间部分的范围为大于等于"08:00",小于等于"12:00"的值,统一改为"09:00".
举个例子吧:如果数据库某时间字段值为"2007-04-28 08:30" 改为"2007-04-28 09:00" ,值为"2007-04-29 10:30"改为"2007-04-29 09:00",应该明白我的意思了吧?呵呵,谢谢指教!
举个例子吧:如果数据库某时间字段值为"2007-04-28 08:30" 改为"2007-04-28 09:00" ,值为"2007-04-29 10:30"改为"2007-04-29 09:00",应该明白我的意思了吧?呵呵,谢谢指教!
insert #t
select '2007-04-29 10:30' union all
select '2007-04-28 08:30' union all
select '2007-04-28 14:30'
更新:
update #t set tm=cast(convert(varchar(10),tm,120)+' 09:00' as datetime)
where convert(varchar(8),tm,108) between '08:00:00' and '12:00:00'查看结果
select * from #t
----------
tm
2007-04-29 09:00:00.000
2007-04-28 09:00:00.000
2007-04-28 14:30:00.000
insert tt select '2007-04-28 08:30'
union all select '2007-04-28 07:30'
union all select '2007-04-28 09:30'
union all select '2007-04-28 12:30'
union all select '2007-04-28 09:00'update tt set dt=convert(datetime,convert(char(10),dt,120))+'09:00'
where dt>=convert(datetime,convert(char(10),dt,120))+'08:00'
and dt<convert(datetime,convert(char(10),dt,120))+'12:00'select * from tt
drop table ttdt
------------------------------------------------------
2007-04-28 09:00:00.000
2007-04-28 07:30:00.000
2007-04-28 09:00:00.000
2007-04-28 12:30:00.000
2007-04-28 09:00:00.000