470,648 Members | 1,602 Online

# Finding number of weekdays given from date and todate. 111 100+
This article will explain how we can get the count of weekdays in between two dates. This will be usefull if we want to count the number of working days between two dates.

Example:
-------------
USE [DataBaseName]
GO

--This function is used to count the number of weekdays between
-- two dates.
create function udf_Weekdays(@Weekday,@BeginDate datetime,@EndDate datetime) returns integer
begin

--@Weekday: 1 = Monday , ... ,7 = Sunday

return (select datediff(week,@BeginDate,@EndDate) + case when (@@datefirst + datepart(weekday,@BeginDate)) % 7 + case when (@@datefirst + datepart(weekday,@BeginDate)) % 7 = 0 then 7 else 0 end > @Weekday % 7 + 1 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@EndDate)) % 7 + case when (@@datefirst + datepart(weekday,@EndDate)) % 7 = 0 then 7 else 0 end >= @Weekday % 7 + 1 then 0 else 1 end)

end

Run:
-----
Exec udf_Weekdays(7,'12/14/2008',''12/25/2008')
This will return 2, there are two sundays between '12/14/2008' and '12/25/2008'.

If you want the working days for the given dates, simply do the datediff for the dates given and minus the number of saturdays and sundays between these dates by using the above function udf_Weekdays.

Thanks
Bharath Reddy VasiReddy
Sep 17 '09 #1
1 6836 Thank u.. Last one week i search for this expected answer only.. thank u very much...
Oct 13 '10 #2