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

16 years ago

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 )

SQL DateTime, SQL Function, SQL Scripts, SQL Utility

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

SQL SERVER – 2005 Enable CLR using T-SQL script

18 Comments. Leave new

Dal
May 5, 2007 11:48 am
Alexander is good author. Thanks for bringing this to here.
Reply
mike
July 25, 2007 7:20 pm
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
mike
July 25, 2007 7:32 pm
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
Chris
August 13, 2007 12:34 pm
Missing “=” from line
WHEN (@LastPart 0) THEN @LastPart – 1
Should be
WHEN (@LastPart = 0) THEN @LastPart – 1
Reply
Clinton
August 15, 2007 12:26 am
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
Ben
November 15, 2007 5:55 pm
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
shms
March 12, 2008 9:12 am
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
Wieland
April 14, 2008 9:21 pm
I think the missing sign should be a ‘>’, not an ‘=’.
i.e.
WHEN (@LastPart > 0) THEN @LastPart – 1
Reply
red
June 6, 2008 11:21 pm
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
HM
November 18, 2008 2:35 am
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
Muthuvel
March 10, 2010 4:37 pm
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
- BT-MicroLink
  October 14, 2010 2:11 am
  –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
  - pinaldave
    October 14, 2010 11:30 am
    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,
BT-MicroLink
October 27, 2010 11:27 pm
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
BT-MicroLink
October 27, 2010 11:27 pm
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
Vams
August 29, 2011 8:12 pm
@BT-Microlink
The latest function still has missing logic.Can you please repost the complete function again.
Thanks!
Reply
Jithendar
January 7, 2014 8:11 pm
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
- Rosa
  February 21, 2015 3:22 am
  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