use Date::Manip http://www.perldoc.com/perl5.6.1/lib/Date/Manip.html
-J -----Original Message----- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:33 PM To: 'Lucas Cowgar'; MySQL Mailing List Subject: RE: # of Business Days? >>Can anyone out there help me with a SQL query? I need to find the number of business days between date a and b? >>Obviously finding simply the number of days is easy, but I have no clue how to find the number of business days. >>TIA! Here's some ropey perl I wrote a ages back to calculate working minutes between two unix timestamps (based on UK bank holidays and working hours of 9 to 6). I'm sure you could adapt to whatever you need.... Please - no-one correct me on my perl (I already know....), Don't fix what ain't broke :) Even so - I would reccoment some changes on a system that uses the below heavily... Cheers, Andrew ### Used to calculate working minutes between two unix timestamps # # $sdate = 1040807553; # $edate = 1040809553; # # $result = calc_workmins($sdate, $edate); ################################################################## sub calc_workmins { local ($sdate,$edate) = @_; $mins_total = 1; $rows = 0; while ($sdate le $edate) { my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($sdate); $year += 1900; $mon += 1; ## perl returns months in the range 0 to 11 my ($todaysdate) = $mday . "-" . $mon . "-" . $year; my $bankflag = 0; for my $bankhol ("1-1-2002","29-3-2002","1-4-2002","3-6-2002","4-6-2002","26-8-2002","25-12- 2002","26-12-2002","1-1-2003","18-4-2003","21-4-2003","5-5-2003","26-5-2003" ,"25-8-2003","25-12-2003","26-12-2003","27-12-2003","28-12-200","31-1-2004", "9-4-2004","12-4-2004","3-5-2004","31-5-2004","30-8-2004","25-12-2004","26-1 2-2004","27-12-2004","3-1-2005","25-3-2005","28-3-2005","2-5-2005","30-5-200 5","29-8-2005","25-12-2005","26-12-2005") { if ($todaysdate == $bankhol) { $bankflag = 1; } $bankholshow = $bankhol; } if (($hour > 8) && ($hour < 19) && ($wday > 0) && ($wday < 6) && ($bankflag < 1)) { $mins_total++; } $sdate = $sdate + 60; $rows++; } return $mins_total; } --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
