SQL SERVER – UDF – User Defined Function to Find Weekdays Between Two Dates

Following user defined function returns number of weekdays between two dates specified. This function excludes the dates which are passed as input params. It excludes Saturday and Sunday as they are weekends. I always had this function with for reference but after some research I found original source website of the function. This function has been written by Author Alexander Chigrik.

CREATE FUNCTION dbo.spDBA_GetWeekDays
( @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) 0) THEN @LastPart - 1
ELSE 0
END
SELECT
@WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN
( @WorkDays )
END
GO

This function can be used as
SELECT dbo.spDBA_GetWeekDays ('10/10/2005', '11/22/2005')
GO

Reference : Pinal Dave (https://blog.sqlauthority.com) , Alexander Chigrik )

, , ,
Previous Post
SQL SERVER – Fix : Error : Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO for OLE DB provider SQLNCLI for linked server LinkedServerName
Next Post
SQL SERVER – 2005 Enable CLR using T-SQL script

Related Posts

18 Comments. Leave new

  • Alexander is good author. Thanks for bringing this to here.

    Reply
  • here is the complete text…

    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 0) THEN @LastPart – 1
    ELSE 0
    END
    SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
    END
    RETURN ( @WorkDays )
    END
    GO

    Reply
  • also if you want this to be inclusive, add this right below the begin statement and before the first declare

    select @StartDate = @StartDate -1
    select @EndDate = @EndDate + 1

    Reply
  • Missing “=” from line
    WHEN (@LastPart 0) THEN @LastPart – 1
    Should be
    WHEN (@LastPart = 0) THEN @LastPart – 1

    Reply
  • Hi. I get the following message when attempting to add your function. Could you please confirm this works for you

    Msg 102, Level 15, State 1, Procedure GetWorkingDays, Line 18
    Incorrect syntax near ‘@FirstPart’.
    Msg 102, Level 15, State 1, Procedure GetWorkingDays, Line 51
    Incorrect syntax near ‘END’.

    Reply
  • I did one like this:

    CREATE FUNCTION [dbo].[WeekdaysCount]
    ( @StartDate datetime,
    @EndDate datetime )
    RETURNS INT
    AS
    BEGIN

    DECLARE @TotalDays int, @WorkDays int, @Sats int, @Suns int

    IF (DATEDIFF(day, @StartDate, @EndDate) = 0)
    BEGIN
    RETURN ( 0 )
    END

    SET @TotalDays = DATEDIFF(day, @StartDate, @EndDate)
    SELECT @Sats = DATEDIFF(ww,@StartDate,@EndDate) + CASE WHEN DATENAME(dw,@StartDate) = ‘sunday’ THEN 1 ELSE 0 END,
    @Suns = DATEDIFF(ww,@StartDate,@EndDate) + CASE WHEN DATENAME(dw,@EndDate) = ‘saturday’ THEN 1 ELSE 0 END

    SET @WorkDays = @TotalDays – @Sats – @Suns

    RETURN ( @WorkDays )
    END
    GO

    Reply
  • how do i taking holidays when calculating number of working days. because i already create table holiday for store all weekend holidays and public holiday.

    Reply
  • I think the missing sign should be a ‘>’, not an ‘=’.

    i.e.
    WHEN (@LastPart > 0) THEN @LastPart – 1

    Reply
  • if you want to know weekdays fractional to the second …

    create function dbo.WeekDays(@StartDate datetime, @EndDate datetime)
    returns float
    as
    begin
    return (
    (cast(datediff(s, @StartDate, @EndDate) as float) / 86400)
    -(2*DateDiff(ww,@StartDate,@EndDate))+
    case when datepart(dw, @StartDate)=7 then 1 else 0 end
    )
    end

    Reply
  • I would like to search for DataLength(AcctNo) = ‘2’ and if first position is = ‘0’ then make AcctNo = 10x where x is second position character of acctno.

    Reply
  • DECLARE @TotalDays INT,@WorkDays INT
    DECLARE @ReducedDayswithEndDate INT
    DECLARE @WeekPart INT
    DECLARE @DatePart INT

    SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1
    SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
    WHEN ‘Saturday’ THEN 1
    WHEN ‘Sunday’ THEN 2
    ELSE 0 END
    SET @TotalDays=@TotalDays-@ReducedDayswithEndDate
    SET @WeekPart=@TotalDays/7;
    SET @DatePart=@TotalDays%7;
    SET @WorkDays=(@WeekPart*5)+@DatePart

    RETURN @WorkDays

    Reply
    • –Extending on the contribution by Muthuvel to accommodate date variances less than 1 week or when dates are less than 1 week and are same day or Sat. to Sun.

      FUNCTION [dbo].[WeekDayDiff]

      (
      @StartDate datetime,
      @EndDate datetime
      )
      RETURNS int

      AS

      BEGIN

      DECLARE @TotalDays INT
      DECLARE @WorkDays INT
      DECLARE @WeekendEndDate INT
      DECLARE @Weeks INT
      DECLARE @FinalWeekDays INT

      SET @TotalDays = DATEDIFF(day, @StartDate, @EndDate)
      SELECT @WeekendEndDate = CASE DATENAME(weekday, @EndDate)
      WHEN ‘Saturday’ THEN 1
      WHEN ‘Sunday’ THEN 2
      ELSE 0
      END
      SET @TotalDays = CASE WHEN DATEPART(dw,@StartDate) > DATEPART(dw,@EndDate) THEN @TotalDays – @WeekendEndDate – 2
      ELSE @TotalDays – @WeekendEndDate
      END
      SET @Weeks = @TotalDays / 7;
      SET @FinalWeekDays = @TotalDays % 7;
      SET @WorkDays = CASE WHEN @Weeks = 0 AND (@StartDate = @EndDate OR (DATENAME(weekday, @StartDate) = ‘Saturday’ AND DATENAME(weekday, @EndDate) = ‘Sunday’)) THEN 0
      WHEN @Weeks = 0 AND @StartDate @EndDate THEN @TotalDays
      ELSE (@Weeks * 5) + @FinalWeekDays
      END

      RETURN @WorkDays

      END

      Reply
      • Hi,

        If you try your query you will get a bit different result.

        SELECT dbo.WeekDayDiff(GETDATE()-10, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-9, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-8, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-7, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-6, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-5, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-4, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-3, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-2, GETDATE());
        SELECT dbo.WeekDayDiff(GETDATE()-1, GETDATE());

        Result:
        ———–
        8
        ———–
        7
        ———–
        6
        ———–
        5
        ———–
        4
        ———–
        3
        ———–
        4
        ———–
        3
        ———–
        2
        ———–
        1

        Would you please check?

        Kind Regards,

  • Duly noted. Some of my own testing revealed issues when the @StartDate was after the @EndDate. Revised function:

    CREATE FUNCTION [dbo].[WeekDayDiff]

    (
    @StartDate datetime,
    @EndDate datetime
    )
    RETURNS int

    AS

    BEGIN

    DECLARE @TotalDays INT
    DECLARE @WorkDays INT
    DECLARE @WeekendStartDate INT
    DECLARE @WeekendEndDate INT
    DECLARE @Weeks INT
    DECLARE @FinalWeekDays INT

    SET @TotalDays = DATEDIFF(day, @StartDate, @EndDate)
    SELECT @WeekendStartDate = CASE DATENAME(weekday, @StartDate)
    WHEN ‘Saturday’ THEN 1
    WHEN ‘Sunday’ THEN 1
    ELSE 0
    END
    SELECT @WeekendEndDate = CASE DATENAME(weekday, @EndDate)
    WHEN ‘Saturday’ THEN 1
    WHEN ‘Sunday’ THEN 1
    ELSE 0
    END
    SET @TotalDays = CASE WHEN DATEPART(dw,@StartDate) > DATEPART(dw,@EndDate) AND @StartDate < @EndDate THEN @TotalDays – 2
    WHEN DATEPART(dw,@StartDate) <= DATEPART(dw,@EndDate) AND @StartDate <= @EndDate THEN @TotalDays – (@WeekendStartDate + @WeekendEndDate)
    WHEN DATEPART(dw,@StartDate) @EndDate THEN @TotalDays + 2
    WHEN DATEPART(dw,@StartDate) >= DATEPART(dw,@EndDate) AND @StartDate >= @EndDate THEN @TotalDays + (@WeekendStartDate + @WeekendEndDate)
    END
    SET @Weeks = @TotalDays / 7;
    SET @FinalWeekDays = @TotalDays % 7;
    SET @WorkDays = CASE WHEN @Weeks = 0 AND (@StartDate = @EndDate OR @WeekendStartDate + @WeekendEndDate = 2) THEN 0
    WHEN @Weeks = 0 AND @StartDate @EndDate THEN @TotalDays
    ELSE (@Weeks * 5) + @FinalWeekDays
    END

    RETURN @WorkDays

    END

    GO

    Reply
  • Test for revised function:

    DECLARE @Date AS datetime
    SET @Date = ’10-27-2010′

    SELECT dbo.WeekDayDiff(DATEADD(d,-10,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-9,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-8,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-7,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-6,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-5,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-4,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-3,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-2,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,-1,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,0,@Date), @Date);

    SELECT dbo.WeekDayDiff(DATEADD(d,10,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,9,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,8,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,7,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,6,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,5,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,4,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,3,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,2,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,1,@Date), @Date);
    SELECT dbo.WeekDayDiff(DATEADD(d,0,@Date), @Date);

    Reply
  • @BT-Microlink

    The latest function still has missing logic.Can you please repost the complete function again.
    Thanks!

    Reply
  • CREATE FUNCTION dbo.getDayCount(@startdate date, @enddate date)
    RETURNS int
    AS
    BEGIN
    declare @count int = 0
    while @startdate<=@enddate
    begin
    if DATEPART(dw,@startdate)1 and DATEPART(dw,@startdate)7
    begin
    set @count = @count + 1
    end
    set @startdate = dateadd(day,1,@startdate)
    end
    return @count
    END

    Reply
    • Missing from line
      if DATEPART(dw,@startdate)1 and DATEPART(dw,@startdate)7
      Should be
      if DATEPART(dw,@startdate) 1 and DATEPART(dw,@startdate) 7

      and it’s work… Thanks!!!

      Reply

Leave a Reply

Menu