今天遇到1个很是无语的问题。。。由于开发人员的版本更新导致用户在今天所开之单据的CreatDate都变成了yyyy-MM-dd格式,原本应该为
yyyy-MM-dd hh:mm:ss:fff格式。于是,只能手动更新数据了 -_-!
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 declare @itemnumber int --定义需要循环的次数
2 declare @tagint int --定义标志字段,用于结束循环
3 set @tagint=1
4 select @itemnumber = count(distinct Creater) from Demo_TestTable where isnull(Creater,'')<>'' And
DATEDIFF(DAY,CreatDate,GETDATE())<1
5 if(@itemnumber>0)
6 begin
7 while @tagint<=@itemnumber
8 begin
9 waitfor delay '00:00:01' --每隔一秒再执行 可用参数变量替换
10 Update Demo_TestTable set CreatDate=GETDATE() where Creater =(
11 Select Creater from (
12 select Creater,ROW_NUMBER() over(order by Creater) as RowID from Demo_TestTable where
isnull(Creater,'')<>'' And DATEDIFF(DAY,CreatDate,GETDATE())<1 group by Creater
13 ) TableA
14 where TableA.RowID=@tagint
15 )
16 set @tagint=@tagint+1
17 end
18 end
2 declare @tagint int --定义标志字段,用于结束循环
3 set @tagint=1
4 select @itemnumber = count(distinct Creater) from Demo_TestTable where isnull(Creater,'')<>'' And
DATEDIFF(DAY,CreatDate,GETDATE())<1
5 if(@itemnumber>0)
6 begin
7 while @tagint<=@itemnumber
8 begin
9 waitfor delay '00:00:01' --每隔一秒再执行 可用参数变量替换
10 Update Demo_TestTable set CreatDate=GETDATE() where Creater =(
11 Select Creater from (
12 select Creater,ROW_NUMBER() over(order by Creater) as RowID from Demo_TestTable where
isnull(Creater,'')<>'' And DATEDIFF(DAY,CreatDate,GETDATE())<1 group by Creater
13 ) TableA
14 where TableA.RowID=@tagint
15 )
16 set @tagint=@tagint+1
17 end
18 end
当然今天出现的情况因其特殊性有更简单的写法,但此法具体一般性. @tagint即类似于For循环中的i,通过在查询时设置Row_Number()行号来遍历得到每1个需要Update的ID,进而进行相关的处理