| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Implicit Date conversion problem
What does "doesn't work" mean?
Wrong data?
Error message?
etc...??
Also the trunc(to_date('01-Jan-03','DD-Mon-YY')) is not quite right most likely.
It should probably be:
trunc(to_date('01-Jan-03','DD-Mon-YY'))
to_date('01-Jan-03 23:59:59','DD-Mon-YY' HH24:MI:SS')
or if used with a variable:
trunc(mydate+1) - (1/86400)
Jared
On Tuesday 29 October 2002 05:54, Jamadagni, Rajendra wrote:
> We have a query (please don't ask me why it is written this way)
>
> --Doesn't work:
> select ea1.ep_number, ea1.est_dt, veas.est_dt
> from (select a.ep_number, ea.est_dt
> from v_episode_avail_summary_break a, episode_airings ea
> where a.ep_number = ea.ep_number
> and ea.est_dt between '20-Dec-02' and '01-Jan-03'
> group by a.ep_number, ea.est_dt) veas,
> episode_airings ea1
> where veas.ep_number = ea1.ep_number
> and ea1.est_dt between '20-Dec-02' and '01-Jan-03'
>
> vs.
>
> --Works:
> select ea1.ep_number, ea1.est_dt, veas.est_dt
> from (select a.ep_number, ea.est_dt
> from v_episode_avail_summary_break a, episode_airings ea
> where a.ep_number = ea.ep_number
> and ea.est_dt between '20-Dec-02' and '01-Jan-03'
> group by a.ep_number, ea.est_dt) veas,
> episode_airings ea1
> where veas.ep_number = ea1.ep_number
> and ea1.est_dt between trunc(to_date('20-Dec-02','DD-Mon-YY'))
> and trunc(to_date('01-Jan-03','DD-Mon-YY'))
>
> Also if we comment "and ea1.est_dt between '20-Dec-02' and '01-Jan-03'" the
> non-working query runs fine.
> Does anyone know what could be wrong? The est_dt is a date column. This is
> 9201 ...
>
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of ESPN
> Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: ---------------------------------------- ---------------------------------------- Content-Type: text/plain; charset="iso-8859-1"; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: jkstill_at_cybcon.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Oct 29 2002 - 09:28:56 CST
 |
 |