新建表t中有Fdate 和FcreateDate 2个字段,求一个更新语句,如果Fdate的时间部分等于00:00:000, 那么更新Fdate字段,更新Fdate的值为Fdate的日期+FcrDate的时间。例如Fid 1的Fdate时间为00:00:000,那么就更新Fdate='2011-01-01 13:54:17.813',也就是一个取日期一个取时间拼起来
主要难点在一个判断一个拼接写法,请教高人
create table t(Fid int,Fdate datetime,FcrDate datetime)insert t(Fid,Fdate,FcrDate )values(1,'2011-01-01 00:00:000','2011-09-06 13:54:17.813')
insert t(Fid,Fdate,FcrDate )values(2,'2011-09-06 14:04:17.833','2011-09-06 14:04:17.833')select * from t
这样?
if replace(replace(convert(varchar(8),Fdate,108),'0',''),':','')=''
update ...
Fdate = convert(varchar(10),Fdate,120) + stuff(convert(varchar(23),FcrDate ,121),1,10,'')
where Fdate = convert(varchar(10),Fdate,120)
update t set
Fdate = convert(varchar(10),Fdate,120) +' '+ CONVERT(varchar(100), FcrDate,8)
where Fdate = convert(varchar(10),Fdate,120)
tb
set
Fdate = convert(varchar(10),Fdate,120) +' '+ CONVERT(varchar(100), FcrDate,108)
where
Fdate = convert(varchar(10),Fdate,120)
update t set Fid=dateadd(dd,datediff(dd,FcrDate,Fdate),FcrDate)
where datepart(hh,Fdate)=0 and datepart(mi,Fdate)=0 and datepart(ss,Fdate)=0
--获取直接
update t set Fid=dateadd(dd,datediff(dd,FcrDate,Fdate),FcrDate)
--思路,Fdate的日期+FcrDate的时间
update t set Fdate=dateadd(dd,datediff(dd,FcrDate,Fdate),FcrDate)
--where条件可以不用加
where datepart(hh,Fdate)=0 and datepart(mi,Fdate)=0 and datepart(ss,Fdate)=0