It's not precisely correct. When time difference is less than 7, the time is calcualted wrong
end_time 2005-01-10 17:53:33 end_time 2005-01-04 16:44:57 Result: days 6 Result: bussiness_days 6 On Sat, 19 Feb 2005 09:50:06 -0500, Mike Rains <[EMAIL PROTECTED]> wrote: > On Sat, 19 Feb 2005 14:01:05 +0000, Jerry Swanson <[EMAIL PROTECTED]> wrote: > > I have two dates (start_date, end_date). Datediff() function returns > > difference in days. > > I need the difference but not including Satuday and Sunday. > > > > Any ideas? > > C:\Program Files\MySQL\MySQL Server 4.1\bin>mysql -utest -ptest test > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 43 to server version: 4.1.8-nt > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> CREATE TABLE `DateDiffs` ( > -> start_date DATETIME, > -> end_date DATETIME > -> ); > Query OK, 0 rows affected (0.15 sec) > > mysql> INSERT INTO DateDiffs > -> (start_date, end_date) > -> VALUES > -> ('2005-02-14 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-02-07 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-02-04 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-01-31 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-01-28 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-01-28 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-01-24 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-01-21 00:00:00', '2005-02-18 00:00:00'), > -> ('2005-01-17 00:00:00', '2005-02-18 00:00:00'); > Query OK, 9 rows affected (0.06 sec) > Records: 9 Duplicates: 0 Warnings: 0 > > mysql> SELECT > -> start_date, > -> end_date, > -> datediff(end_date, start_date) > -> AS dd1, > -> datediff(end_date, start_date) - > floor(datediff(end_date, start_date) / 7) * 2 > -> AS dd2 > -> FROM DateDiffs > -> ORDER BY start_date; > +---------------------+---------------------+------+------+ > | start_date | end_date | dd1 | dd2 | > +---------------------+---------------------+------+------+ > | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 32 | 24 | > | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 28 | 20 | > | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 25 | 19 | > | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 | > | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 | > | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 18 | 14 | > | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 14 | 10 | > | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 11 | 9 | > | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | 4 | > +---------------------+---------------------+------+------+ > 9 rows in set (0.00 sec) > > mysql> DROP TABLE DateDiffs; > Query OK, 0 rows affected (0.19 sec) > > mysql> exit > > The column dd1 contains the unaltered DATEDIFF() which includes the > Saturdays and Sundays, while the dd2 column contains the number of > business days omitting the weekend days. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]