SQL Sever - Datediff, hours, but exclude Saturday/ Sundays

Question

I'm wondering how to calculate the "number of hours" between two timestamps (2016-02-24 17:30:00 and another, for instance) in SQL server- but excluding Saturday and Sunday's full 48 hour period, if crossed.

This isn't quite the same as pure business hours, but sort of. The reason for this is long-winded and unnecessary.

EDIT: I can also say that the end-date will always be during the week. So really ... the "start date" can simply be transmuted to Monday midnight, if on Sat/ Sun ... then maybe a function include the total week count...

`datediff(hour, date, date2) - datediff(week, date1, date2) * 48`? — ZLK, Feb 28 '17 at 23:31
It is difficult using a single query, due first or last day could be saturday or sunday. I did something similar but for whole days [here](http://stackoverflow.com/a/42262116/3270427) — McNets, Feb 28 '17 at 23:36
Hmm I took a look at your example and may dig deeper through it, but seems awfully long winded. I'm thinking ... the datediff(hour,date1,date2) is a fine baseline --- is there simply a way to count the "Sunday hours" and "Saturday hours" between two dates? I guess that's the same problem. — user45867, Feb 28 '17 at 23:40
the accepted answer to http://stackoverflow.com/questions/252519/count-work-days-between-two-dates may give a good starting point - will need to adjust for hours rather than days and for cases where the date range starts or ends on a weekend — Ian Kenney, Feb 28 '17 at 23:46
If your start and end date can be on a saturday or sunday, then you'd need to generate all dates between start and end date and then not include those where your datepart(weekday, date) in (1, 7). — ZLK, Feb 28 '17 at 23:54

score 1 · Accepted Answer · answered Feb 28 '17 at 23:50

1

DATEDIFF(Week, date, date2) will return the number of week boundaries that are crossed between the two dates. For SQL Server, this means how many Sundays are between the dates (as opposed to the number of 7 day periods are between them). This means, that if you can indeed assume that start and end date will not be a saturday or sunday, you can subtract 48 X DATEDIFF(Week, date, date2) from your normal DATEDIFF call and that should give you what are after.

answered Feb 28 '17 at 23:50

Tim

5,940
1
12
18

Hello --- that's a good starting point. The END point cannot be a Sat/ Sunday. However, the start date can. I feel like simply one check needs to be made whether the 'start date' is a saturday and sunday --- then transmute this time to the following Monday at midnight if so. Once that occurs, I think your method would work. Hmm -- I think that is all that is necessary. – user45867 Mar 01 '17 at 21:56

score 1 · Answer 2 · answered Mar 01 '17 at 03:20

I would use the below code

declare @NumberOfHours int

declare @StartTime datetime
declare @EndTime datetime

set @StartTime = '2017-02-02 17:30:00.000'
set @EndTime = '2017-02-07 00:00:00.000'

set @NumberOfHours = DATEDIFF(HOUR,@StartTime,@EndTime)

if(datepart(WEEKDAY, @StartTime)=1)
begin
    set @NumberOfHours = @NumberOfHours     DATEDIFF(HH,@StartTime,@EndTime)%24
end
else if(datepart(WEEKDAY, @StartTime)=7)
begin
    set @NumberOfHours = @NumberOfHours - DATEDIFF(HH,@StartTime,@EndTime)%24
    set @NumberOfHours = @NumberOfHours - 24
end
else
begin
    set @NumberOfHours = @NumberOfHours -  datediff(ww,@StartTime,@EndTime)*48
end
print @NumberOfHours

score 0 · Answer 3 · answered Mar 01 '17 at 00:14

I would use a calendar table (ex. dbo.DateDimension, ref https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/):

CREATE TABLE dbo.DateDimension
(
  DateKey             INT         NOT NULL PRIMARY KEY,
  [Date]              DATE        NOT NULL,
  [Day]               TINYINT     NOT NULL,
  DaySuffix           CHAR(2)     NOT NULL,
  [Weekday]           TINYINT     NOT NULL,
  WeekDayName         VARCHAR(10) NOT NULL,
  IsWeekend           BIT         NOT NULL,
  IsHoliday           BIT         NOT NULL,
  ...
)

and, also, following query:

SELECT SUM(
    CASE 
        WHEN dd.[Date] = CONVERT(DATE, @StartDate) THEN DATEDIFF(MINUTE, @StarDate, DATEADD(DAY, 1, dd.[Date]))
        WHEN dd.[Date] = CONVERT(DATE, @EndDate) THEN DATEDIFF(MINUTE, dd.[Date], @EndDate)
        ELSE 24 * 60 -- Full day
    END) / 60 AS SumOfHours
FROM dbo.DateDimension dd
WHERE dd.[Date] >= CONVERT(DATE, @StartDate) AND dd.[Date] <= CONVERT(DATE, @EndDate) 
AND dd.IsWeekend = 0

Above query will compute total amount of minutes for requested period of time and then it will divide by 60 to get number of hours.

SQL Sever - Datediff, hours, but exclude Saturday/ Sundays

3 Answers3