表taba中有字段 firstdate 类型是nvarchar,存的数据如2007-11-20字段cyclenm 类型是int 存的数据为15现在要update 字段nextdate ,类型是 nvarcharupdate中的值是要今天日期以后的,而且是
字段firstdate+N*字段cyclenm来的比方 firstdate='2010-1-01'
cyclenm=3
nextdate='2010-1-28'
字段firstdate+N*字段cyclenm来的比方 firstdate='2010-1-01'
cyclenm=3
nextdate='2010-1-28'
WHERE CONVERT(DATETIME,firstdate)>GETDATE()
tb
set
nextdate=dateadd(dd,n*cyclenm,firstdate)
WHERE CONVERT(DATETIME,nextdate)>GETDATE()
tb
set
nextdate=dateadd(dd,n*cyclenm,firstdate)
where
datediff(dd,firstdate,getdate())<0
update taba
set nextdate = convert(varchar(10),dateadd(day,cyclenm,
case when firstdate>= convert(varchar(10),getdate(),120) then firstdate else getdate() end))
就是firstdate中的值一直加cyclenm,加到得到的值大于今天日期
if object_id('[taba]') is not null drop table [taba]
create table [taba]([firstdate] nvarchar(10),[cyclenm] int,[nextdate] nvarchar(10))
go
insert [taba]
select '2010-1-01',3,nullupdate [taba]
set nextdate = convert(varchar(10),
dateadd(day,ceiling(datediff(day,[firstdate],getdate())*1.0/3)*3,firstdate),120)
select * from taba---------------------------2010-1-01 3 2010-01-28
set nextdate = convert(varchar(10),
dateadd(day,ceiling(datediff(day,[firstdate],getdate())*1.0/cyclenm)*cyclenm,firstdate),120)如果正好等于今天,要不要再往后推?
就用
update [taba]
set nextdate = convert(varchar(10),
dateadd(day,(floor(datediff(day,[firstdate],getdate())*1.0/cyclenm)+1)*cyclenm,firstdate),120)