List:General Discussion« Previous MessageNext Message »
From:Jerry Swanson Date:February 21 2005 2:37pm
Subject:Re: how to write this query?
View as plain text  
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 <sirhammer@stripped> wrote:
> On Sat, 19 Feb 2005 14:01:05 +0000, Jerry Swanson <pmysql@stripped> 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.
>
Thread
how to write this query?Jerry Swanson19 Feb
  • Re: how to write this query?Mike Rains19 Feb
    • Re: how to write this query?Jerry Swanson21 Feb
      • Re: how to write this query?Mike Rains21 Feb
        • RE: how to write this query?Gordon21 Feb