27,579
社区成员
发帖
与我相关
我的任务
分享
select case datepart(weekday,dateadd(day,DateInterval,StartDate)+@@datefirst-1) when 6 then dateadd(day,DateInterval,StartDate)+2
when 7 then dateadd(day,DateInterval,StartDate)+1
else dateadd(day,DateInterval,StartDate) end
SET DATEFIRST 1; -- 设置星期一是一个星期的第1天
SQL默认周日是一个星期的第1天[/quote]不好意思,上面错了,不一定是10天,下面是对的,完整版的:
SET DATEFIRST 1; -- 设置星期一是一个星期的第1天
UPDATE #Test
SET EndDate = CASE DATEPART(dw,DATEADD(d,DateInterval,StartDate))
WHEN 6 THEN DATEADD(d,DateInterval + 2,StartDate) --若是周六顺延2天
WHEN 7 THEN DATEADD(d,DateInterval + 1,StartDate) --若是周日顺延1天
ELSE DATEADD(d,DateInterval,StartDate)
END
SET DATEFIRST 1; -- 设置星期一是一个星期的第1天
SQL默认周日是一个星期的第1天UPDATE #Test
SET EndDate = DATEADD(d,DateInterval + CASE DATEPART(dw,StartDate)
WHEN 3 THEN 2 -- 周三加10天是周六,故要顺延2天
WHEN 4 THEN 1 -- 周四加10天是周日,故要顺延1天
ELSE 0
END,StartDate)
用函数,楼上几位没理解人家的意思,10天里面要扣掉周六周日
-- 个人理解,LZ是在考虑 自然日的工作日。
-- 两层子查询,有点多,你把你的日期替换掉 getdate() , 你的那个间隔替换掉 10 。
SELECT MAX(rn)
FROM
( SELECT top 10 rn
FROM
( SELECT GETDATE() + ROW_NUMBER() over(ORDER BY id) - 1 rn FROM sysobjects) x
WHERE DATEPART(weekday,rn) NOT IN ('7','1')
) m
CREATE FUNCTION [dbo].[udf__DaysExceptWeekDay] (@StartDate DATETIME, @EndDate DATETIME)
/***************************************************************************
1.Created Date : Created User Name : chen
2.Modified Date : Modified User Name :
3.Content :
返回一段时间内的 不包括 周末的 天数
4.Program List :
***************************************************************************/
RETURNS INT
BEGIN
DECLARE @Date INT
IF DATEPART(dw, @StartDate) = 7
BEGIN
SET @StartDate = DATEADD(dd, 2, @StartDate)
END
ELSE IF DATEPART(dw, @StartDate) = 1
BEGIN
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
IF DATEPART(dw, @EndDate) = 7
BEGIN
SET @EndDate = DATEADD(dd, -1, @EndDate)
END
ELSE IF DATEPART(dw, @EndDate) = 1
BEGIN
SET @EndDate = DATEADD(dd, -2, @EndDate)
END
IF @EndDate >= @StartDate
SET @Date = DATEDIFF(dd, @StartDate, @EndDate) - DATEDIFF(wk, @StartDate, @EndDate) * 2 + 1
ELSE
SET @Date = 0
RETURN @Date
END
--------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test
create table #test(StartDate datetime,DateInterval int)
insert #test
select '2014-05-15',10 union all
select '2014-05-14',10 union all
select '2014-05-13',10 union all
select '2014-05-12',10 union all
select '2014-05-11',10 union all
select '2014-05-10',10
SELECT StartDate
,WeekDay
,DateInterval
,DaysExceptWeekDay
,EndDate
,EndDateExceptWeekDay
,CASE DATEPART(weekday,CAST(EndDateExceptWeekDay AS DATETIME))-1
WHEN 1 THEN N'星期一'
WHEN 2 THEN N'星期二'
WHEN 3 THEN N'星期三'
WHEN 4 THEN N'星期四'
WHEN 5 THEN N'星期五'
WHEN 6 THEN N'星期六'
WHEN 0 THEN N'星期日'
ELSE N'世界末日' END AS EndWeekDay
FROM (
SELECT StartDate
,WeekDay
,DateInterval
,DaysExceptWeekDay
,CASE WHEN DATEPART(weekday,DATEADD(DAY,DateInterval,StartDate))=1
THEN DATEADD(DAY,DateInterval+1, StartDate)
WHEN DATEPART(weekday,DATEADD(DAY,DateInterval,StartDate))=7
THEN DATEADD(DAY,DateInterval+2, StartDate)
ELSE DATEADD(DAY,DateInterval,StartDate) END AS EndDate
,CASE WHEN DATEPART(weekday,DATEADD(DAY,DaysExceptWeekDay,StartDate))=1
THEN DATEADD(DAY,DaysExceptWeekDay+1, StartDate)
WHEN DATEPART(weekday,DATEADD(DAY,DaysExceptWeekDay,StartDate))=7
THEN DATEADD(DAY,DaysExceptWeekDay+2, StartDate)
ELSE DATEADD(DAY,DaysExceptWeekDay,StartDate) END AS EndDateExceptWeekDay --you want this?
FROM (
SELECT StartDate
,CASE DATEPART(weekday,CAST(StartDate AS DATETIME))-1
WHEN 1 THEN N'星期一'
WHEN 2 THEN N'星期二'
WHEN 3 THEN N'星期三'
WHEN 4 THEN N'星期四'
WHEN 5 THEN N'星期五'
WHEN 6 THEN N'星期六'
WHEN 0 THEN N'星期日'
ELSE N'世界末日' END AS [WeekDay]
,DateInterval
,dbo.udf_CSLK_DaysExceptWeekDay(StartDate,DATEADD(DAY,DateInterval,StartDate)) AS DaysExceptWeekDay
FROM #test
) AS B
) AS T
create table test(StartDate datetime,DateInterval int)
insert test
select '2014-05-15',10 union all
select '2014-05-14',10 union all
select '2014-05-13',10 union all
select '2014-05-12',10 union all
select '2014-05-11',10 union all
select '2014-05-10',10
select StartDate,
DateInterval,
case when datepart(WEEKDAY,(DATEADD(DAY,DateInterval,StartDate)))=1 then DATEADD(DAY,DateInterval+1,StartDate)
when datepart(WEEKDAY,(DATEADD(DAY,DateInterval,StartDate)))=7 then DATEADD(DAY,DateInterval+2,StartDate)
else DATEADD(DAY,DateInterval,StartDate)
end
from test
/*
2014-05-15 00:00:00.000 10 2014-05-26 00:00:00.000
2014-05-14 00:00:00.000 10 2014-05-26 00:00:00.000
2014-05-13 00:00:00.000 10 2014-05-23 00:00:00.000
2014-05-12 00:00:00.000 10 2014-05-22 00:00:00.000
2014-05-11 00:00:00.000 10 2014-05-21 00:00:00.000
2014-05-10 00:00:00.000 10 2014-05-20 00:00:00.000
*/
use tempdb
declare @list table(StartDate varchar(10),DateInterval int)
insert into @list
select '2014-05-15',10
union all select '2014-05-01',2
union all select '2014-05-05',2
--select * from @list
select StartDate,DateInterval,
dateadd(d,DateInterval,StartDate)+
case datepart(dw,dateadd(d,DateInterval,StartDate))
when 1 then 1 when 7 then 2 else 0 end
from @list a
/*
2014-05-15 10 2014-05-26 00:00:00.000
2014-05-01 2 2014-05-05 00:00:00.000
2014-05-05 2 2014-05-07 00:00:00.000
*/