Sorry, but I've omitted part of the where clause to make the message shorter
and now I see that it was important. Here goes the select again:
SELECT A.cod_call, A.cod_link, A.nr_call , A.status,
B.dthr_occurrence as open,
DATE_SUB(C.dthr_occurence, INTERVAL B.dthr_occurrence HOUR_MINUTE) AS
during,
DATE_SUB(D.dthr_occurrence, INTERVAL B.dthr_occurrence HOUR_MINUTE)
AS late
FROM CALL A, OCCURRENCE B, OCCURRENCE C, OCCURRENCE D
WHERE A.COD_CALL = B.COD_CALL AND
A.COD_CALL = C.COD_CALL AND
A.COD_CALL = D.COD_CALL AND
A.COD_LINK = '$lnk' AND
B.DESC_OCCURRENCE = 'open' AND
C.DESC_OCCURRENCE = 'close' AND
D.DESC_OCCURRENCE = 'stop' AND
(B.DTHR_OCCURRENCE BETWEEN '$datef' AND '$datet')
----- Original Message -----
From: "Keith C. Ivey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Felipe D. Ramalho" <[EMAIL PROTECTED]>
Sent: Wednesday, November 20, 2002 3:09 PM
Subject: Re: datetime arithmetic
> On 20 Nov 2002, at 14:40, Felipe D. Ramalho wrote:
>
> > SELECT ..., B.dthr_occurrence as open,
> > DATE_SUB(C.dthr_occurrence, INTERVAL B.dthr_occurrence
> > HOUR_MINUTE) AS during,
> > DATE_SUB(D.dthr_occurrence, INTERVAL B.dthr_occurrence
> > HOUR_MINUTE) AS late
> >
> > FROM CALL A, OCCURRENCE B, OCCURRENCE C, OCCURRENCE D
>
> The DATE_SUBs in your query make no sense. You're treating the same
> column as both a date and an "HH:MM" string representing a duration
> in hours and minutes. It can't be both.
>
> --
> Keith C. Ivey <[EMAIL PROTECTED]>
> Tobacco Documents Online
> http://tobaccodocuments.org
> Phone 202-667-6653
>
>
---------------------------------------------------------------------
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
