开发者社区> 问答> 正文

如何在MS SQL Server中查找两个日期(除周末外)之间的小时数?

如何在MS SQL Server中查找两个日期(除周末外)之间的小时数?

展开
收起
贺贺_ 2019-12-02 19:41:12 400 0
1 条回答
写回答
取消 提交回答
  • 此查询返回除所有星期日和星期六之外的两个日期之间的确切天数、月份和小时):

    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
    
    2019-12-02 19:42:01
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Server 2017 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载