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