Count work days between two dates in T-SQL (Extended)

Question

Extending this question: Here

I would like to count work days between two dates in t-sql, just weekends but not the whole work day (24 hrs) just let's say from 9 AM to 5:30 PM each work day.

How can I do that using for instance CMS' approach:

 DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


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)

score 1 · Answer 1 · edited Jan 12 '15 at 16:41

1

I think you pretty well had it, just need to change the THEN 1 to THEN -1

However, I'm not entirely sure what you'd like to do with the hours part of your question.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/03/01'
SET @EndDate = '2013/03/31'


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)

edited Jan 12 '15 at 16:41

AHiggins

7,029
6
36
54

answered Mar 18 '13 at 22:08

crosan

486
4
13

The hours part is the business hours of the work day. In this case I'll have to change the datediff into hours instead of days. Let's say that I want to know the business hours between two dates Monday and tuesday, with above solution I'll have 48 hours (2 days) but with the business hours only (9am to 5pm) would be 16 hours, that's what I need. – Somebody Mar 18 '13 at 22:15

score 1 · Accepted Answer · answered Mar 19 '13 at 13:14

This is what I was looking for, here is the code in case someone else need it:

The parameters are:

@D1 (StartDate)
@D2 (EndDate)
@T1 (Business Hours - Start)
@T2 (Business Hours - End)

The function returns a float with the amount of hours.

CREATE FUNCTION [dbo].[WorkHoursDiff] (@D1 as datetime,@D2 as datetime,@T1 as int,@T2 as int)  
RETURNS float AS  
BEGIN 
    if @D1>@D2 return 0

    declare @whd as float

    declare @H1 as float
    declare @H2 as float


    if not (datepart(yyyy,@D1   )=datepart(yyyy,@D2) and datepart(mm,@D1)=datepart(mm,@D2) and datepart(dd,@D1)=datepart(dd,@D2))
        begin
            declare @DD as datetime
            SET @whd=0
            if datepart(dw,@D1)<>1 and datepart(dw,@D1)<>7 
                                        if dbo.GetDateHour(@D1)<@T1 SET @whd = @T2-@T1
            else if dbo.GetDateHour(@D1)>@T2 SET @whd = 0
            else if dbo.GetDateHour(@D1)>=@T1 and dbo.GetDateHour(@D1)<=@T2 SET @whd = @T2-dbo.GetDateHour(@D1)

            SET @DD=@D1 + 1
            while not (datepart(yyyy,@DD)=datepart(yyyy,@D2) and datepart(mm,@DD)=datepart(mm,@D2) and datepart(dd,@DD)=datepart(dd,@D2))
                begin
                    if not(datepart(dw,@DD)=1 or datepart(dw,@DD)=7)  
                         SET @whd=@whd + (@T2-@T1)

                    SET @DD=@DD + 1
                end
                if datepart(dw,@D2)<>1 and datepart(dw,@D2)<>7
                if dbo.GetDateHour(@D2)<@T1 SET @whd = @whd 
                else if dbo.GetDateHour(@D2)>@T2 SET @whd = @whd + @T2 - @T1
                else if dbo.GetDateHour(@D2)>=@T1  and dbo.GetDateHour(@D2)<=@T2 SET @whd = @whd +dbo.GetDateHour(@D2)-@T1

        end
    else
        begin
            if dbo.GetDateHour(@D1)<@T1 SET @H1=@T1
            else if dbo.GetDateHour(@D1)>@T2 SET @H1=@T2
            else SET @H1=dbo.GetDateHour(@D1)

            if dbo.GetDateHour(@D2)<@T1 SET @H2=@T1
            else if dbo.GetDateHour(@D2)>@T2 SET @H2=@T2
            else SET @H2=dbo.GetDateHour(@D2)

            if datepart(dw,@D1)=1 or datepart(dw,@D1)=7  SET @whd=0
            else SET @whd=@H2-@H1
        end
    return @whd

END

score 0 · Answer 3 · answered Mar 19 '13 at 08:11

found this..

 CREATE FUNCTION dbo.GetWorkingDays
  ( @StartDate datetime,
    @EndDate datetime )
RETURNS INT
AS
BEGIN
  DECLARE @WorkDays int, @FirstPart int
  DECLARE @FirstNum int, @TotalDays int
  DECLARE @LastNum int, @LastPart int
  IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
    BEGIN
      RETURN ( 0 )
    END
  SELECT
   @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
   @FirstPart = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 6
                 WHEN 'Monday' THEN 5
                 WHEN 'Tuesday' THEN 4
                 WHEN 'Wednesday' THEN 3
                 WHEN 'Thursday' THEN 2
                 WHEN 'Friday' THEN 1
                 WHEN 'Saturday' THEN 0
               END,
   @FirstNum = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 5
                 WHEN 'Monday' THEN 4
                 WHEN 'Tuesday' THEN 3
                 WHEN 'Wednesday' THEN 2
                 WHEN 'Thursday' THEN 1
                 WHEN 'Friday' THEN 0
                 WHEN 'Saturday' THEN 0
               END
  IF (@TotalDays < @FirstPart)
     BEGIN
       SELECT @WorkDays = @TotalDays
     END
  ELSE
     BEGIN
       SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
       SELECT @LastPart = (@TotalDays - @FirstPart) % 7
       SELECT @LastNum = CASE
         WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
         ELSE 0
       END
       SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
     END
  RETURN ( @WorkDays )
END
GO

score 0 · Answer 4 · answered Oct 11 '18 at 06:10

Try this

Declare @startdate datetime,
@enddate datetime,
@countSunday int,
@countTuesday int,
@countWednesday int,@countThursday int,@countFriday int,@countSaturday int

set @countSunday=0
set @countMonday=0
set @countTuesday =0
set @countWednesday =0
set @countThursday =0
set @countFriday =0
set @countSaturday=0
set @startdate='2018-10-01'
set @enddate='2018-10-20'

while @startdate<=@enddate
Begin
    IF DatePart(WEEKDAY,@startdate) = 1
    SET @countSunday=@countSunday+1
    else if  DatePart(WEEKDAY,@startdate) = 2
      SET @countMonday=@countMonday+1  
      else if  DatePart(WEEKDAY,@startdate) = 3
      SET @countTuesday=@countTuesday+1  
      else if  DatePart(WEEKDAY,@startdate) = 4
      SET @countWednesday=@countWednesday+1  
      else if  DatePart(WEEKDAY,@startdate) = 5
      SET @countThursday=@countThursday+1  
      else if  DatePart(WEEKDAY,@startdate) = 6
      SET @countFriday=@countFriday+1  
      else if  DatePart(WEEKDAY,@startdate) = 7
      SET @countSaturday=@countSaturday+1  
    SET @startdate=DateAdd(d,1,@startdate)
END


select @countSunday as Sunday ,@countMonday as Monday,@countTuesday as Tuesday,@countWednesday as Wednesday,@countThursday as Thursday,@countFriday as Friday,@countSaturday as Saturday

score 0 · Answer 5 · answered Oct 11 '18 at 07:53

Try this

DECLARE @DateFrom DateTime ='20181001' , @DateTo DateTime = '20181031'  ;
WITH CTE(dt)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DATEADD(d, 1, dt) FROM CTE
  WHERE dt < @DateTo
)
SELECT datename("dw", dt) as Days,count(datename("dw", dt)) as numberofDays FROM CTE 
group by datename ("dw", dt)

Count work days between two dates in T-SQL (Extended)

5 Answers5