472,811 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,811 developers and data experts.

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.

USE [DataBaseName]

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

--@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)


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.

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

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

Similar topics

by: Tiernan | last post by:
Hi all I'm looking for a way to find the number of weekdays between 2 dates In my form I have three fields for a begin date (dd)(mm)(yyyy) and three for the end date (dd)(mm)(yyyy) Now these...
by: Hugh Welford | last post by:
Hi... I am in UK using XP with date format set to dd/mm/yy. I am running an asp site from a server in USA, which is returning the date from a database field retrieved as objrec("todate") in US...
by: John | last post by:
hey all..... alright, I am frusterated to the point of throwing my machine out the window (this board went down, trying to find stuff on google, this has been a nightmare) so I hope you guys can...
by: SimonC | last post by:
Help needed for a Javascript beginner. As above in the subject... i need a javascript to run this, but not in the form of a web-page. I want to calculate it between 2 fields in a database that...
by: toddw607 | last post by:
Hi Everyone, I am using Access 2003 to input 2 dates from text boxes to display a report. The following is the code I'm trying to run: Dim fromDate As Date Dim toDate As Date ...
by: atiq | last post by:
I am trying to restrict the user to only enter date which is weekdays. so, it shouldn't allow a date that is weekend such as 08/04/07 (Sunday). Can someone help me with this issue. In short i want...
by: brnkstyle | last post by:
I figured out how to calculate the work day given two values but all of my records have dates and i want to be able to calcuate the average time it takes to do a job per month so basically take all...
by: SANTHOSH KUMAR POVAI | last post by:
Dim fromdate, todate As String fromdate = Mid(fromdatetxt, 4, 2) + "/" + Mid(fromdatetxt, 1, 2) + "/" + Mid(fromdatetxt, 7, 4) todate = Mid(todatetxt, 4, 2) + "/" + Mid(todatetxt, 1, 2) + "/" +...
by: kyosohma | last post by:
Hi, I am working on a timesheet application in which I need to to find the first pay period in a month that is entirely contained in that month to calculate vacation time. Below are some example...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.