CODEWITHSUNDEEP
sql-serverdatetimedatediffbusiness-logic
user45867
user45867

Reputation: 983

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

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...

Upvotes: 0

Views: 2800

Answers (3)

Aundy
Aundy

Reputation: 66

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

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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.

Upvotes: 0

Tim
Tim

Reputation: 6060

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.

Upvotes: 1

Related Questions