select case when datediff(dd,'1900-01-01',时间字段) %7 = 0 then dateadd(-1,dd,时间字段) end from 表
更正一下 select case when datediff(dd,'1900-01-01',时间字段) %7 = 0 then dateadd(dd,-1,时间字段) end from 表
update teble set time1= dateadd(day,-1,time1) where ..
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
update teble set time1= dateadd(day,-1,time1) where datediff(dd,'1900-01-01',时间字段) %7 = 0
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
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' exec(@str) --取下一条记录 fetch next from DZCursor into @tablename, @colname end --关闭游标 close DZCursor --删除游标引用 deallocate DZCursor
--先备份数据 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+'')=1 '' 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) '
--先备份数据 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)
select case when datediff(dd,'1900-01-01',时间字段) %7 = 0 then dateadd(-1,dd,时间字段) end
from 表
select case when datediff(dd,'1900-01-01',时间字段) %7 = 0 then dateadd(dd,-1,时间字段) end
from 表
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
--用游标
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
--用游标
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'
exec(@str)
--取下一条记录
fetch next from DZCursor into @tablename, @colname
end
--关闭游标
close DZCursor
--删除游标引用
deallocate DZCursor
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+'')=1 ''
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)
'
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)