470,648 Members | 1,602 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Tiernan | last post: by
3 posts views Thread by Hugh Welford | last post: by
10 posts views Thread by kyosohma | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.