457,933 Members | 1,574 Online Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

# Finding number of weekdays given from date and todate.

 100+ P: 111 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 