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 )

## 18 Comments. Leave new

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

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

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

Missing “=” from line

WHEN (@LastPart 0) THEN @LastPart – 1

Should be

WHEN (@LastPart = 0) THEN @LastPart – 1

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

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

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.

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

i.e.

WHEN (@LastPart > 0) THEN @LastPart – 1

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

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.

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

–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

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

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);

@BT-Microlink

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

Thanks!

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

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