ALTER trigger gzdjbd on a001a001
for update
as
declare @changedate datetime,@nowdate datetime,@a0100 varchar(50)
select @changedate=aa.a5505,@a0100=aa.a0100
from a055a001 aa,a001a001 cc,(select a0100,max(id) as a_id from a055a001 group by a0100) bb
where aa.a0100=bb.a0100 and aa.a0100=cc.a0100 and bb.a0100=cc.a0100 and bb.a_id=aa.idselect @nowdate=convert(char(8),current_timestamp)
--if substring(convert(char(10),@nowdate),7,4)=substring(convert(char(10),@changedate),7,4)
--begin
while substring(convert(char(10),@nowdate),7,4)=substring(convert(char(10),@changedate),7,4)
begin
while (substring(convert(char(10),@nowdate),1,2)-1=substring(convert(char(10),@changedate),1,2))
begin
if substring(convert(char(10),@changedate),5,2)>15 --and substring(convert(char(10),@changedate),5,2)<=31)
begin
update a001a001
set c0132='11'
where a001a001.a0100=@a0100
end
else
begin
update a001a001
set c0132='12'
where a001a001.a0100=@a0100
end
end
end
这个分析能过得去,但就是更新没反映,不知是哪里有问题,请高手过招
for update
as
declare @changedate datetime,@nowdate datetime,@a0100 varchar(50)
select @changedate=aa.a5505,@a0100=aa.a0100
from a055a001 aa,a001a001 cc,(select a0100,max(id) as a_id from a055a001 group by a0100) bb
where aa.a0100=bb.a0100 and aa.a0100=cc.a0100 and bb.a0100=cc.a0100 and bb.a_id=aa.idselect @nowdate=convert(char(8),current_timestamp)
--if substring(convert(char(10),@nowdate),7,4)=substring(convert(char(10),@changedate),7,4)
--begin
while substring(convert(char(10),@nowdate),7,4)=substring(convert(char(10),@changedate),7,4)
begin
while (substring(convert(char(10),@nowdate),1,2)-1=substring(convert(char(10),@changedate),1,2))
begin
if substring(convert(char(10),@changedate),5,2)>15 --and substring(convert(char(10),@changedate),5,2)<=31)
begin
update a001a001
set c0132='11'
where a001a001.a0100=@a0100
end
else
begin
update a001a001
set c0132='12'
where a001a001.a0100=@a0100
end
end
end
这个分析能过得去,但就是更新没反映,不知是哪里有问题,请高手过招
if substring(convert(char(10),@changedate),5,2)>15 --and substring(convert(char(10),@changedate),5,2)<=31)
begin
update a001a001
set c0132='11'
from a001a001
where a001a001.a0100=@a0100
end
else
begin
update a001a001
set c0132='12'
from a001a001
where a001a001.a0100=@a0100
end
------------------------------------------------------
这句类型根本就不匹配.一个datetime,一个是char.
2: 去掉while 的判断,直接用if 判断。然后用print 跟踪你的变量值的变化情况。看是否没有满足你的条件。很快就会找到原因。
select @nowdate=convert(char(8),current_timestamp)
------------------------------------------------------
这句类型根本就不匹配.一个datetime,一个是char.
-----------------
:)
不要武斷的下結論,測試下先吧。
select @nowdate=convert(char(8),current_timestamp)
print convert(varchar(20),@nowdate,120)
------------------------------------------
结果
2020-08-03 00:00:00
------------------------------------------------------
恩,是可以的.原来这样也行,看来只靠经验感觉是不行的。
不过 char(8)要换成varchar(20)才行.
--------------------------------------------------------
declare @nowdate datetime
select @nowdate=convert(varchar(20),current_timestamp)
print convert(varchar(20),@nowdate,120)
------------------------------------------
结果
2007-08-03 00:00:00
select @changedate=aa.a5505,@a0100=aa.a0100
from a055a001 aa,a001a001 cc,(select a0100,max(id) as a_id from a055a001 group by a0100) bb
where aa.a0100=bb.a0100 and aa.a0100=cc.a0100 and bb.a0100=cc.a0100 and bb.a_id=aa.idselect @nowdate=convert(datetime,current_timestamp)
--if substring(convert(char(10),@nowdate),7,4)=substring(convert(char(10),@changedate),7,4)
--begin
if substring(convert(varchar(20),@nowdate),7,4)=substring(convert(char(10),@changedate),7,4)
begin
print 'yes'
end
else
begin
print 'no'
end
这个日期比较法不对吗?