By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,933 Members | 1,574 Online
Bytes IT Community
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
Share this Article
Share on Google+
1 Comment


P: n/a
Arun Vasu Lynx
Thank u.. Last one week i search for this expected answer only.. thank u very much...
Oct 13 '10 #2