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]
