Many a times it is required to find working days between two dates. We do know that DATEDIFF function can be used to find differences between two dates; however the working days calculation excludes weekends and holidays. This solution doesn’t consider holidays. The function for the same is given below.
CREATE FUNCTION [dbo].fn_CountWeekDays
(
@fromdate Datetime,
@todate Datetime
)
RETURNS TABLE AS RETURN
(
SELECT
(DATEDIFF(dd, @fromdate, @todate) + 1)
-(DATEDIFF(wk, @fromdate, @todate) * 2)
-(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END)
As NoOfWeekDays
)
The above function is an inline table valued function which accepts two parameters @fromdate and @todate. The statement DATEDIFF(dd,@fromdate,@todate) + 1 gives the number of dates between the two dates. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it’s a Saturday or Sunday.
The output from the function is given below.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
declare @startdate date,
@enddate date, @a integer
set @startdate=’2016-04-01′
set @enddate=’2016-04-30′
set @a=0
select @startdate
select @enddate
while @startdate<=@enddate
Begin
if Datename(dw,@startdate)in('Monday','Tuesday','Wednesday','Thursday','Friday')
begin
set @a=@a+1
end
set @startdate=DATEADD(d,1,@startdate)
end;
select @a
Hello everyone,
I am struggling with the following problem. I need to calculate hours between 2 dates. For example let say that the start day is 01.03.2018 -Thursday and the end day is 06.03.2018 – Tuesday. I want to be able to calculate only the working hours between these 2 days. Let say that working hours are between 9 and 18. I tried a few things but no luck. Any help will be much appreciated. Thanks
thnk you for this function i really lookiing for it but a find it at the end thnks , a hane a small qu’estion what about if a want to add the Holidays also to the weekends