更新資料庫中所有日期欄位,周日減一天

-----------------------方法1

Declare @T Varchar(255),@C Varchar(255)
Declare Table_Cursor Cursor For
Select A.Name,B.Name From Sysobjects A,Syscolumns B Where A.Id=B.Id And A.Xtype='u' And (B.Xtype=58 Or B.Xtype=61)

Open Table_Cursor
Fetch Next From  Table_Cursor Into @T,@C

While(@@Fetch_Status=0)
Begin
    Exec('SET DATEFIRST 1   update ['+@T+'] Set ['+@C+']=['+@C+']-1 where datepart(dw,['+@C+'])=7')
    Fetch Next From  Table_Cursor Into @T,@C
End
Close Table_Cursor
Deallocate Table_Cursor

 

-------------------------方法2

declare @tablename varchar(100),@colname varchar(50)
--用遊標
declare @str varchar(100)
--定義遊標
declare DZCursor CURSOR for select b.name tablename,a.name colname from syscolumns a,sysobjects b where a.id=b.id and b.xtype='u' and object_id(b.name)>0 and a.xtype in (58,61) order by b.name,a.name
--開啟遊標
open  DZCursor
--從遊標取記錄
fetch next from DZCursor into @tablename, @colname
--當有記錄
while @@fetch_status=0
begin
set @str='update '+@tablename+' set '+@colname+'=dateadd(dd,-1,'+@colname+') where datepart(dw,'+@colname+')=1'
print @str
--取下一條記錄
fetch next from DZCursor into @tablename, @colname
end
--關閉遊標
close DZCursor
--刪除遊標引用
deallocate DZCursor

 

-----------------------------方法3

--先備份資料
EXEC SP_MSFOREACHTABLE N'
DECLARE @STR VARCHAR(8000)
SET @STR=''''
SELECT @STR=@STR+'' UPDATE ''+O.NAME+'' SET ''+C.NAME+''=DATEADD(DAY,-1,''+C.NAME+'') WHERE DATEPART(DW,''+C.NAME+'')=7 ''
FROM SYSCOLUMNS C JOIN SYSOBJECTS O ON C.ID=O.ID 
WHERE O.ID=OBJECT_ID(''?'') AND (C.xtype=58 or C.xtype=61)
--PRINT ''SET DATEFIRST 1''+@STR
EXEC(''SET DATEFIRST 1''+@STR)
'