如何在MS SQL Server中查找两个日期（除周末外）之间的小时数？

此查询返回除所有星期日和星期六之外的两个日期之间的确切天数、月份和小时）：

SELECT (DATEDIFF(MINUTE, @d1, @d2) - DATEDIFF(WK, @d1, @d2) * 2880) +
CASE
WHEN DATEDIFF(WK, @d1, @d2) = 1 AND DATEDIFF(DW, @d1, @d2) <= 5 THEN 0
WHEN DATEDIFF(WK, @d1, @d2) = 0 THEN 0 
ELSE 1440 END 

SELECT (DATEDIFF(HOUR, @d1, @d2) - DATEDIFF(WK, @d1, @d2) * 48) + 
CASE 
WHEN DATEDIFF(WK, @d1, @d2) = 1 AND DATEDIFF(DW, @d1, @d2) <= 5 THEN 0 WHEN DATEDIFF(WK, @d1, @d2) = 0 THEN 0 
ELSE 24 END 

SELECT (DATEDIFF(DW, @d1, @d2) - DATEDIFF(WK, @d1, @d2) * 2) + 
CASE 
WHEN DATEDIFF(WK, @d1, @d2) = 1 AND DATEDIFF(DW, @d1, @d2) <= 5 THEN 0 WHEN DATEDIFF(WK, @d1, @d2) = 0 THEN 0 
ELSE 1 END

或者，您可以使用此泛型函数：

CREATE FUNCTION [dbo].[fn_GetBusinnessDaysTimeSpan](
 @DateFrom DATETIME,
 @DateTO DATETIME,
 @Type VARCHAR(50) 
)
RETURNS BIGINT 
AS 
BEGIN 
DECLARE @result AS BIGINT; 
IF @Type = 'Min' 
BEGIN 
SET @result = (SELECT (DATEDIFF(MINUTE, @DateFrom, @DateTO) - DATEDIFF(WK, @DateFrom, @DateTO) * 2880) + 
CASE 
WHEN DATEDIFF(WK, @DateFrom, @DateTO) = 1 AND (DATEDIFF(DW, @DateFrom, @DateTO)) <= 5 THEN 0 
WHEN DATEDIFF(WK, @DateFrom, @DateTO) = 0 THEN 0 
ELSE 1440 END 
) 
END 

IF @Type = 'Hour' 
BEGIN 
SET @result = (SELECT (DATEDIFF(HOUR, @DateFrom, @DateTO) - DATEDIFF(WK, @DateFrom, @DateTO) * 48) + 
CASE 
WHEN DATEDIFF(WK, @DateFrom, @DateTO) = 1 AND (DATEDIFF(DW, @DateFrom, @DateTO)) <=5 THEN 0 
WHEN DATEDIFF(WK, @DateFrom, @DateTO) = 0 THEN 0 
ELSE 24 END 
) 
END 

IF @Type = 'Day' 
BEGIN 
SET @result = (SELECT (DATEDIFF(DW, @DateFrom, @DateTO) - DATEDIFF(WK, @DateFrom, @DateTO) * 2) + 
CASE 
WHEN DATEDIFF(WK, @DateFrom, @DateTO) = 1 AND (DATEDIFF(DW, @DateFrom, @DateTO)) <=5 THEN 0 
WHEN DATEDIFF(WK, @DateFrom, @DateTO) = 0 THEN 0 
ELSE 1 END 
) 
END 
RETURN @result 
END