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]
