set nocount on
go
-- 计算N天前的日期(不含星期六,日)
alter proc ppp
@nowdate datetime,-- @nowdate 可能是星期一到星期日中的任何一天
@n int
as
set datefirst 1 -- 星期- 到星期天 依次为1,2,3,4,5,6,7
if datepart(weekday,@nowdate)=7
set @n=@n+ case when @n+1>=datepart(weekday,@nowdate) then ((@n-datepart(weekday,@nowdate))/5+1)*2 else 0 end +1
else
set @n=@n+ case when @n>=datepart(weekday,@nowdate) then ((@n-datepart(weekday,@nowdate))/5+1)*2 else 0 end
select dateadd(day,-@n,@nowdate)
go
-------------------------------------------------------
-- 计算N天前(后)的日期(不含星期日)
set nocount on
go
alter proc ppp
@nowdate datetime,-- @nowdate 可能是星期一到星期日中的任何一天
@n int -- 求@N天前(后)的日期(不含星期日)
AS
set datefirst 1 -- 星期- 到星期天 依次为1,2,3,4,5,6,7
DECLARE @nowweek int
set @nowweek= datepart(weekday,@nowdate)
IF @n<=0 -- 计算 n 天前的日期
set @n=-(abs(@n)+ case when abs(@n)>=@nowweek then (abs(@n)-@nowweek)/6+1 else 0 end )
ELSE -- 计算 n 天后的日期
IF @nowweek=7
set @n=@n+ case when @n+@nowweek>=7 then (@n-1)/6 else 0 end
ELSE
set @n=@n+ case when @n+@nowweek>6 then (@n+@nowweek-1)/6 else 0 end
select dateadd(day,@n,@nowdate)
go