如何添加到以下腳本以僅在 10:00 <> 18:00 小時內額外計算時間。如果時間@StartDate是 09:00 那么我們把它當作 10:00
例子：
SET @StartDate = '2021/12/10 09:00:00'
SET @EndDate = '2021/12/11 18:30:00'
預期的分鐘結果是從 10:00 到 18:00 的分鐘總和，因此 8*60 = 480 分鐘。
謝謝你的提示
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/03/15 23:30:00'
SET @EndDate = '2013/03/17 00:30:00'
SELECT
( DATEDIFF(MINUTE, @StartDate, @EndDate)
- ( DATEDIFF(wk, @StartDate,@EndDate)*(2*24*60)
-- End on Sunday
-(CASE WHEN DATEPART(dw, @EndDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
-- Start on Saturday
-(CASE WHEN DATEPART(dw, @StartDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
-- End on Saturday
(CASE WHEN DATEPART(dw, @EndDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
-- Start on Saturday
(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
)
)
uj5u.com熱心網友回復：
嘗試這個。
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @MinDateTime DATETIME
DECLARE @MaxDateTime DATETIME
SET @StartDate = '2021/12/10 09:00:00'
SET @EndDate = '2021/12/10 18:30:00'
SET @MinDateTime = CONVERT(datetime, CONCAT(CONVERT(DATE, @StartDate), ' 10:00:00'))
SET @MaxDateTime = CONVERT(datetime, CONCAT(CONVERT(DATE, @EndDate), ' 18:00:00'))
SELECT
( DATEDIFF(MINUTE, @StartDate, @EndDate)
- ( DATEDIFF(wk, @StartDate,@EndDate)*(2*24*60)
-- End on Sunday
-(CASE WHEN DATEPART(dw, @EndDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
-- Start on Saturday
-(CASE WHEN DATEPART(dw, @StartDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
-- End on Saturday
(CASE WHEN DATEPART(dw, @EndDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
-- Start on Saturday
(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
)
),
DATEDIFF(minute, CASE WHEN @StartDate < @MinDateTime
THEN @MinDateTime
ELSE @StartDate
END,
CASE WHEN @EndDate > @MaxDateTime
THEN @MaxDateTime
ELSE @EndDate
END) AS diff
測驗它演示
uj5u.com熱心網友回復：
只是另一種選擇（蠻力）。
可以跨越多天。可以擴展到包括假期。
Declare @Date1 datetime = '2021/12/10 10:00:00'
Declare @Date2 datetime = '2021/12/12 18:30:00'
Select Minutes = count(*)
From ( Select Top (DateDiff(Minute,@Date1,@Date2) 1) D=DateAdd(MINUTE,-1 Row_Number() Over (Order By (Select Null)),@Date1)
From master..spt_values n1,master..spt_values n2
) A
Where datepart(WEEKDAY,D) not in (1,7)
and convert(time,D) >= '10:00'
and convert(time,D) < '18:00'
and convert(date,D) not in ('2021-12-25','2022-01-01') -- Holidays: Should be a table
結果
Minutes
480
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/377484.html
標籤：sql sql-server 查询语句
上一篇：列作為值/SQL中的非透視表