Tsql Datediff Only Business Hours

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 I need to know the date diff in hours assuming that in a day i can count the

Solution 1:

Use a Recursive CTE to get all Hours with Dates.

METHOD 1 : Get all dates with hours between FromDate and 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
   UNIONALLSELECT DATEADD(HH,1,FROMDATE)
   FROM CTE
   WHERE FROMDATE<@TODATE 
)
SELECT ISNULL(CAST(CAST(FROMDATE ASDATE)ASVARCHAR(12)),'Tot')FROMDATE,
CAST(COUNT(FROMDATE)ASVARCHAR(4))+'hrs' [HOURS] 
FROM CTE
WHERE DATEPART(HH,FROMDATE) BETWEEN9AND16AND DATENAME(DW,FROMDATE)<>'SATURDAY'AND DATENAME(DW,FROMDATE)<>'SUNDAY'GROUPBYCAST(FROMDATE ASDATE)
WITHROLLUP

• SQL FIDDLE

METHOD 2 : Gets missing dates between FromDate and ToDate with 8 as hardcoded as Hrs

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 ToDateSELECT  DATEADD(DAY,1,@FROMDATE) FROMDATE,8 HRS
   UNIONALLSELECT DATEADD(DAY,1,FROMDATE),8FROM CTE
   WHERE FROMDATE < DATEADD(DAY,-1,@TODATE) 
)
,CTE2 AS
(
   -- Gets the Hours for FromDateSELECTCAST(@FROMDATEASDATE) DATES, CAST(CAST(DATEDIFF
   (
      MINUTE,@FROMDATE,CAST(CAST(CAST(@FROMDATEASDATE) ASVARCHAR(12))+' 17:00:00'AS DATETIME)
   )ASNUMERIC(18,2))/60ASDECIMAL(18,0)) HRS
   WHERE DATENAME(DW,@FROMDATE)<>'SATURDAY'AND DATENAME(DW,@FROMDATE)<>'SUNDAY'UNIONALL-- Select Hours in between datesSELECTCAST(FROMDATE ASDATE) NEWDATE,HRS
   FROM CTE
   WHERE DATENAME(DW,FROMDATE)<>'SATURDAY'AND DATENAME(DW,FROMDATE)<>'SUNDAY'UNIONALL-- Select Hours for ToDateSELECTCAST(@TODATEASDATE), CAST(CAST(DATEDIFF
   (
      MINUTE,CAST(CAST(CAST(@TODATEASDATE) ASVARCHAR(12))+' 08:00:00'AS DATETIME),@TODATE
   )ASNUMERIC(18,2))/60ASDECIMAL(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 rowSELECT ISNULL(CAST(DATES ASVARCHAR(20)),'Tot')DATES,
CAST(SUM(HRS)ASVARCHAR(4))+'hrs' HRS
FROM CTE2
GROUPBY DATES
WITHROLLUP

• SQL FIDDLE

Solution 2:

@marco burrometo

Create a static table which will have all the calendar functionality like holiday functionality,saturday and sunday is a holiday. It will help you a lot.