Does anyone know what function I would use to find the difference between two dates but will only include Monday to Friday in its calculation? I have already used the datediff function to get the result as an integer but now I need to take Sat and Sunday out from my result. Thanks!

How exactly would that work? I know datepart would return a number that correspods to the day of the week. Here's an example of what I am looking for datediff(day,xx/xx/2004,yy/yy/2003)= n days. I need to subtract the saturdays and sundays from that result n . How would incorporating datepart in there help. I'm sorrt but I just can't form the syntax in my head.

This code doesn't work if the difference between the start date and end date is greater than or equal to 7, e.g. @aDate = '11/1/2005',@bDate = '11/18/2005'. After trying to analyze your code and try to figure out what was wrong with it, I decided to create my own function instead:

I've been using a function we found on the web a while ago an customized to our needs, it might look a little silly but it works for us, it counts a number of days between DateField1 and DateField2, which we use for measurement reporting.

To make the Jeff's line of code accurate I have made the following change

select DateDiff(dd, @start-1, @end) - DateDiff(ww, @start, @end)*2

-Khurram Iqbal

To make it accurate for what situation? this returns 1 if the start and end date are the same day. Surely there are not any weekdays between 11/23/2005 and 11/23/2005 ?

If the start day or the end day might be on a weekend, then you need to define what to do in those situations in your requirements. To me, it doesn't make sense to allow for this, but if you want to, simply define what you wish to do in that situation and handle it.

For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st -- how many weekdays are between those dates? 0 or 1 or "undefined" (null) ? You must define this. It can be interpreted in any of those ways.

- IF(DOW(DateA) <= DOW(DateB),0,2) -> minus another 2 days only if the day of the week of A is not before the day of the week of B. I.E. if its mon to thurs this will return '-0' if its thursday to mon it will return '-2'