Using only Weekdays and Excluding Weekends when calculating dates

[dprichard]

I have been searching trying to figure out if this is possible. I need to figure out how many days are between certain dates excluding weekends. Is it possible to do this. I have tried searching google, but I guess I am not using the right keywords because the results I am getting back aren't giving me much help. I was thinking about setting up a table and putting in all weekend dates and then pulling in that data, but thought there might be an easier way.

[Fumigator]

There's no built-in function that does that kind of thing that I know of. (I'd like to know about it if it does exist!)

The first thing that came to my mind is count the number of weeks between the two dates and subtract (#weeks*2) from a normal date difference calculation.

So the problem becomes how to count weeks between two dates...

Ya know I just thought of an easier way, but it doesn't use MySQL to do it. Using PHP you'd use a loop to start at one date and increment the date one day at a time, checking the day of the week using the date() function (with the 'w' option), and increment a counter variable if the day of the week is not saturday or sunday. The loop ends when the date reaches the ending date.

Not very elegant, and if the 2 dates are years and years apart it might run kinda slow, but it should work...

[CFMaBiSmAd]

You can use the the msyql TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) function (if you have mysql 5.0 or higher) with WEEK for the unit and it will give you the integral number of weeks between the two dates.

[Fumigator]

Nice... I am going to have to start pestering Hostmonster.com to offer MySQL 5.0+ as an option.

Though... I do think there will be some anomalies when the start date is on a friday and the end date is on a monday-- it will probably not round up that last week and the result will be off by 2 days. Maybe for the OP this is not an issue.