TSQL datediff仅营业时间

本文介绍了TSQL datediff仅营业时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在视图中，这两个日期来自一个表：

In a view have these two dates coming from a table:

2014-12-17 14:01:03.523 - 2014-12-20 09:59:28.783

我需要知道以小时为单位的日期差异在一天中，我可以算出从08 AM和5 PM开始的小时数。

I need to know the date diff in hours assuming that in a day i can count the hours just from 08 AM and 5 PM.

当然，周六和周日绝对不能包括在内。

Of course saturdays and sundays must not be included.

我尝试在select中使用此代码，但我仅在几天内得到了差异，不包括星期六和星期日。

I tried using this code inside the select but i only got the diff in days, excluding saturdays and sundays.

(DATEDIFF(HOUR, convert(datetime,t.EXT_DATAINS-2), convert(datetime,b.EXT_DATAINS-2)) + 1) -(DATEDIFF(wk, convert(datetime,t.EXT_DATAINS-2), convert(datetime,b.EXT_DATAINS-2)) * 2) -(CASE WHEN DATENAME(dw, convert(datetime,t.EXT_DATAINS-2)) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, convert(datetime,b.EXT_DATAINS-2)) = 'Saturday' THEN 1 ELSE 0 END) differenza

例如：

2014-12-17 : 3hrs 2014-12-18 : 8hrs 2014-12-19 : 8hrs 2014-12-20 : 2hrs Tot : 21hrs

推荐答案

使用递归CTE 获取所有带有日期的小时数。

Use a Recursive CTE to get all Hours with Dates.

方法1：获取所有日期，其中包括FromDate和ToDate之间的小时数

DECLARE @FROMDATE DATETIME='2014-12-17 14:01:03.523' DECLARE @TODATE DATETIME='2014-12-20 09:59:28.783' ;WITH CTE AS ( SELECT @FROMDATE FROMDATE UNION ALL SELECT DATEADD(HH,1,FROMDATE) FROM CTE WHERE FROMDATE<@TODATE ) SELECT ISNULL(CAST(CAST(FROMDATE AS DATE)AS VARCHAR(12)),'Tot')FROMDATE, CAST(COUNT(FROMDATE)AS VARCHAR(4))+'hrs' [HOURS] FROM CTE WHERE DATEPART(HH,FROMDATE) BETWEEN 9 AND 16 AND DATENAME(DW,FROMDATE)<>'SATURDAY' AND DATENAME(DW,FROMDATE)<>'SUNDAY' GROUP BY CAST(FROMDATE AS DATE) WITH ROLLUP

• SQL FIDDLE

方法2：获取FromDate和ToDate之间的缺失日期，其硬编码为Hrs的8个

此方法将更易于实施-调整性能

This method will be more implementable - Performance Tuned

DECLARE @FROMDATE DATETIME='2014-12-17 14:01:03.523' DECLARE @TODATE DATETIME='2014-12-20 09:59:28.783' ;WITH CTE AS ( -- Get missing dates between FromDate and ToDate SELECT DATEADD(DAY,1,@FROMDATE) FROMDATE,8 HRS UNION ALL SELECT DATEADD(DAY,1,FROMDATE),8 FROM CTE WHERE FROMDATE < DATEADD(DAY,-1,@TODATE) ) ,CTE2 AS ( -- Gets the Hours for FromDate SELECT CAST(@FROMDATE AS DATE) DATES, CAST(CAST(DATEDIFF ( MINUTE,@FROMDATE,CAST(CAST(CAST(@FROMDATE AS DATE) AS VARCHAR(12))+' 17:00:00' AS DATETIME) )AS NUMERIC(18,2))/60 AS DECIMAL(18,0)) HRS WHERE DATENAME(DW,@FROMDATE)<>'SATURDAY' AND DATENAME(DW,@FROMDATE)<>'SUNDAY' UNION ALL -- Select Hours in between dates SELECT CAST(FROMDATE AS DATE) NEWDATE,HRS FROM CTE WHERE DATENAME(DW,FROMDATE)<>'SATURDAY' AND DATENAME(DW,FROMDATE)<>'SUNDAY' UNION ALL -- Select Hours for ToDate SELECT CAST(@TODATE AS DATE), CAST(CAST(DATEDIFF ( MINUTE,CAST(CAST(CAST(@TODATE AS DATE) AS VARCHAR(12))+' 08:00:00' AS DATETIME),@TODATE )AS NUMERIC(18,2))/60 AS DECIMAL(18,0)) WHERE DATENAME(DW,@TODATE)<>'SATURDAY' AND DATENAME(DW,@TODATE)<>'SUNDAY' ) -- Use ROLLUP to find the sum of Hours and show it in last row SELECT ISNULL(CAST(DATES AS VARCHAR(20)),'Tot')DATES, CAST(SUM(HRS)AS VARCHAR(4))+'hrs' HRS FROM CTE2 GROUP BY DATES WITH ROLLUP
• SQL字段