RE: Implicit Date conversion problem

[Mercadante, Thomas F]

Title: Implicit Date conversion problem

Raj,

 

you are forgetting how dates are stored internally vs. how they are displayed by sqlplus.

 

change your first query to:

 

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 to_date('20-Dec-2002','dd-mon-yyyy') and to_date('01-Jan-2003' ,'dd-mon-yyyy')
         group by a.ep_number, ea.est_dt) veas,
       episode_airings ea1
where veas.ep_number = ea1.ep_number
  and ea1.est_dt between between to_date('20-Dec-2002','dd-mon-yyyy') and to_date('01-Jan-2003' ,'dd-mon-yyyy')

Your selection was not working properly because you ended up comparing string values with string values.

 

example:  21-Dec-2002 is not between 20-Dec-2002 and 01-Jan-2003 using string comparison

                    (21 is greater than 20 and 01 both).

 

hope this helps.

 

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 29, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Subject: Implicit Date conversion problem

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!