从日期计算中排除星期六和星期日 [英] exclude saturday and sunday from date calculating
本文介绍了从日期计算中排除星期六和星期日的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在计算日期时排除星期六和星期日。例如,如果我的日期是5,从今天算起,我需要结果日为12/26/2012。
解决方案
如何排除周六和周日时间在计算两个日期之间的小时数差异时? [ ^ ]
计算日期差异不包括周末 [ ^ ]
使用sql server获取DATEDIFF,不包括周末 [ ^ ]
日期计算不包括周末&假期 [ ^ ]
尝试
DECLARE @Date DATETIME
SET @Date = GETDATE()
DECLARE @ DaysToAdd INT
SET @ DaysToAdd = 5
DECLARE @DayOfWeek INT
SELECT @ DayOfWeek = CASE WHEN DATEN AME(w, @ Date )= ' Sunday' 那么 1
WHEN DATENAME(w, @ Date )= ' 星期一' 那么 2
WHEN DATENAME(w, @ Date )= ' 星期二' 那么 3
WHEN DATENAME(w, @ Date )= ' 星期三 那么 4
当 DATENAME(w, @ Date )= ' 星期四' 那么 5
WHEN DATENAME(w, @ Date )= ' 星期五' 那么 6
WHEN DATENAME(w, @ Date )= ' 星期六' 那么 7 结束
IF @ DaysToAdd + @ DayOfWeek < 7
BEGIN
SELECT CONVERT ( VARCHAR ,DATEADD(DAY, @ DaysToAdd , @ Date ), 101 )
END
ELSE
BEGIN
SELECT CONVERT ( VARCHAR ,DATEADD(DAY,@ DaysToAdd + 2, @ Date ), 101 )
END
如果添加的天数超过7,上面的示例将给出错误的结果。所以我想出了另一种方法。
< span class =code-keyword> DECLARE @ Date DATETIME
< span class =code-keyword> SET @ Date = GETDATE()
DECLARE @ DaysToAdd INT
SET @ DaysToAdd = 5
DECLARE @ Count INT
DECLARE @ TotalWeekEnds INT
SET @ TotalWeekEnds = 0
DECLARE @ Phase INT
SET @ Phase = 0
CREATE TABLE #Dates
(
阶段 INT ,
日期 日期时间
)
; WITH CTE(n,日期) AS
(
SELECT 0 AS n, @Date AS 日期
UNION ALL
SELECT n + 1 AS n,DATEADD(d,n + 1, @ Date ) AS 日期
< span class =code-keyword> FROM CTE
WHERE n< @ DaysToAdd
)
INSERT INTO #Dates
SELECT 1 ,日期 FROM CTE
SELECT @ TotalWeekEnds = COUNT(*) FROM #Dates
WHERE (DATENAME(w,日期)= ' 星期日' 或 DATENAME(w,日期)= ' 星期六')
WHILE @ TotalWeekEnds != 0
BEGIN
SET @ Count = 0
SELECT @ Phase = MAX(阶段) + 1 FROM #Dates
WHILE @ Count < @ TotalWeekEnds
BEGIN
INSERT INTO #Dates
SELECT @ Phase ,DATEADD(d, 1 ,MAX( Date )) FROM #Dates
SET @Count = @ Count + 1
结束
SELECT @ TotalWeekEnds = COUNT( *) FROM #Dates
WHERE (DATENAME(w,日期)= ' 星期日' OR DATENAME(w,日期)= ' 星期六') AND 阶段= @阶段
END
- SELECT * FROM #Dates
SELECT CONVERT ( VARCHAR ( 10 ),MAX(日期), 101 ) FROM #Dates
DROP TABLE #Dates
[/ Edit]
i want to exclude saturday and sunday while calculating date.for example if my date period is 5, and calculating from today , i need the result day as 12/26/2012.
解决方案
How to exclude Saturday and Sunday hours while calculating the difference of hours between two dates?[^]
Calculating a date difference excluding weekends[^]
get DATEDIFF excluding weekends using sql server[^]
Date Calculation Excluding Weekend & Holiday[^]
Try
DECLARE @Date DATETIME SET @Date = GETDATE() DECLARE @DaysToAdd INT SET @DaysToAdd = 5 DECLARE @DayOfWeek INT SELECT @DayOfWeek = CASE WHEN DATENAME(w,@Date) = 'Sunday' THEN 1 WHEN DATENAME(w,@Date) = 'Monday' THEN 2 WHEN DATENAME(w,@Date) = 'Tuesday' THEN 3 WHEN DATENAME(w,@Date) = 'Wednesday' THEN 4 WHEN DATENAME(w,@Date) = 'Thursday' THEN 5 WHEN DATENAME(w,@Date) = 'Friday' THEN 6 WHEN DATENAME(w,@Date) = 'Saturday' THEN 7 END IF @DaysToAdd + @DayOfWeek < 7 BEGIN SELECT CONVERT(VARCHAR,DATEADD(DAY,@DaysToAdd, @Date),101) END ELSE BEGIN SELECT CONVERT(VARCHAR,DATEADD(DAY,@DaysToAdd+2, @Date),101) END
[Edit]
The above sample will give incorrect result if the number of days being added is more than 7. So i have come up with another approach.
DECLARE @Date DATETIME SET @Date = GETDATE() DECLARE @DaysToAdd INT SET @DaysToAdd = 5 DECLARE @Count INT DECLARE @TotalWeekEnds INT SET @TotalWeekEnds = 0 DECLARE @Phase INT SET @Phase = 0 CREATE TABLE #Dates ( Phase INT, Date DateTime ) ;WITH CTE(n,Date) AS ( SELECT 0 AS n, @Date AS Date UNION ALL SELECT n+1 AS n, DATEADD(d,n+1, @Date) AS Date FROM CTE WHERE n < @DaysToAdd ) INSERT INTO #Dates SELECT 1, Date FROM CTE SELECT @TotalWeekEnds = COUNT(*) FROM #Dates WHERE (DATENAME(w,Date) = 'Sunday' OR DATENAME(w,Date) = 'Saturday') WHILE @TotalWeekEnds != 0 BEGIN SET @Count = 0 SELECT @Phase = MAX(Phase) + 1 FROM #Dates WHILE @Count < @TotalWeekEnds BEGIN INSERT INTO #Dates SELECT @Phase, DATEADD(d,1,MAX(Date)) FROM #Dates SET @Count = @Count + 1 END SELECT @TotalWeekEnds = COUNT(*) FROM #Dates WHERE (DATENAME(w,Date) = 'Sunday' OR DATENAME(w,Date) = 'Saturday') AND Phase = @Phase END --SELECT * FROM #Dates SELECT CONVERT(VARCHAR(10),MAX(Date),101) FROM #Dates DROP TABLE #Dates
[/Edit]
这篇关于从日期计算中排除星期六和星期日的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文