Different between two dates excluding US holidays hours

Question

SELECT CONVERT(VARCHAR(12), (
            datediff(ss, RIM.Createddate, Getdate()) - (
                SELECT dbo.fn_GetHolidaysAndSaturdayAndSundayCount(RIM.Createddate, Getdate())
                ) * 24 * 60 * 60
            ) / 60 / 60 / 24) + ' Day(s) ' + ':' + CONVERT(VARCHAR(12), (
            datediff(ss, RIM.Createddate, Getdate()) - (
                SELECT dbo.fn_GetHolidaysAndSaturdayAndSundayCount(RIM.Createddate, Getdate())
                ) * 24 * 60 * 60
            ) / 60 / 60 % 24) + ' Hour(s) '
FROM requestinstancemt RIM

Suppose RIM.Createddate = '2014-08-31 23:00:00.000'. I want to exclude the saturday and sunday and holiday and I want to return the hours for only working days.

The query gives only the count. I have tried to change the count to hours It is not working.

possible duplicate of [Count work days between two dates](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) — Tanner, Sep 03 '14 at 11:06
problem is we have to take holiday hours and need to subtract from the original hours. — user3619447, Sep 03 '14 at 11:18
I would suggest create a table for Calendar, since holiday for US & UK is defined we can easily create calendar for future years as well, Once its ready then we can easily query and get working Days between two Dates.. For more details please refer URL http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html — Naveen Kumar, Sep 03 '14 at 11:50
I have done that. problem is getting the holidays hours between two date. that is between'2014-08-31 23:00:00.000' and getdate() — user3619447, Sep 03 '14 at 12:04

score 1 · Accepted Answer · edited May 23 '17 at 12:12

One way....

CREATE TABLE #Days (ActualDay Date, DayOf varchar(10), Holiday bit)

INSERT #Days SELECT '9/1/2014', DATENAME(dw,'9/1/2014'), 1
INSERT #Days SELECT '9/2/2014', DATENAME(dw,'9/2/2014'), 0
INSERT #Days SELECT '9/3/2014', DATENAME(dw,'9/3/2014'), 0
INSERT #Days SELECT '9/4/2014', DATENAME(dw,'9/4/2014'), 0
INSERT #Days SELECT '9/5/2014', DATENAME(dw,'9/5/2014'), 0
INSERT #Days SELECT '9/6/2014', DATENAME(dw,'9/6/2014'), 0
INSERT #Days SELECT '9/7/2014', DATENAME(dw,'9/7/2014'), 0
INSERT #Days SELECT '9/8/2014', DATENAME(dw,'9/8/2014'), 0
INSERT #Days SELECT '9/9/2014', DATENAME(dw,'9/9/2014'), 0
INSERT #Days SELECT '9/10/2014', DATENAME(dw,'9/10/2014'), 0

SELECT COUNT(*) 
FROM #Days
WHERE ActualDay BETWEEN '9/1/2014' AND '9/9/2014'
    AND Holiday = 0
    AND DayOf NOT IN ('Saturday', 'Sunday')

Another option (some code used from Count work days between two dates) ...

CREATE TABLE #Holidays (Holiday Date)


INSERT #Holidays SELECT '1/1/14'
INSERT #Holidays SELECT '1/20/14'
INSERT #Holidays SELECT '2/17/14'
INSERT #Holidays SELECT '4/18/14'
INSERT #Holidays SELECT '5/26/14'
INSERT #Holidays SELECT '7/4/14'
INSERT #Holidays SELECT '9/1/14'
INSERT #Holidays SELECT '10/13/14'
INSERT #Holidays SELECT '11/11/14'
INSERT #Holidays SELECT '11/27/14'
INSERT #Holidays SELECT '12/25/14'


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Holidays int
SET @StartDate = '1/1/14'
SET @EndDate = '1/3/14'

SELECT @Holidays = COUNT(*) FROM #Holidays WHERE Holiday BETWEEN @StartDate AND @EndDate

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) 
  - @Holidays

Different between two dates excluding US holidays hours

1 Answers1