Message-Id: <10735.126037@fatcity.com> From: Jacques Kilchoer Date: Mon, 8 Jan 2001 12:04:02 -0800 Subject: RE: Date comparison question This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C079AE.252DC1C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > -----Original Message----- > From: Lisa Yates [mailto:cosnit@creighton.edu] > Sent: lundi, 8. janvier 2001 11:27 > > Why doesn't this query ever return... >=20 > where run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy=20 > hh24:mi:ss') >=20 > but this query does.... >=20 > where to_char(run_date) =3D to_char(to_date('01-03-2001 > 16:34:59','mm-dd-yyyy hh24:mi:ss')) >=20 > and so does this query.... >=20 > where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001=20 > 16:34:59' I was able to duplicate your results by inserting a row in a table with = a "negative" date (aka a "BCE" date). SQL> create table t (d date) ; Table created. SQL> insert into t (d) values (to_date ('-2001/01/03 16:34:59', = 'SYYYY/MM/DD HH24:MI:SS')) ; 1 row created. SQL> select count (*) 2 from t 3 where d =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss') = ; COUNT(*) ---------- 0 SQL> select count (*) 2 from t 3 where to_char(d) =3D to_char(to_date('01-03-2001 = 16:34:59','mm-dd-yyyy hh24:mi:ss')) 4 ; COUNT(*) ---------- 1 SQL> select count (*) 2 from t 3 where to_char(d,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001 16:34:59' = ; COUNT(*) ---------- 1 select count (*) from t where d =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss') ; Jacques R. Kilcho=EBr (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com=20 ------_=_NextPart_001_01C079AE.252DC1C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Date comparison question

> -----Original Message-----
> From: Lisa Yates [mailto:cosnit@creighton.edu]
> Sent: lundi, 8. janvier 2001 11:27
>
> Why doesn't this query ever return...
>
> where run_date =3D to_date('01-03-2001 = 16:34:59','mm-dd-yyyy
> hh24:mi:ss')
>
> but this query does....
>
> where to_char(run_date) =3D = to_char(to_date('01-03-2001
> 16:34:59','mm-dd-yyyy hh24:mi:ss'))
>
> and so does this query....
>
> where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = =3D '01-03-2001
> 16:34:59'


I was able to duplicate your results by inserting a = row in a table with a "negative" date (aka a "BCE" = date).

SQL> create table t (d date) ;

Table created.

SQL> insert into t (d) values (to_date = ('-2001/01/03 16:34:59', 'SYYYY/MM/DD HH24:MI:SS')) ;

1 row created.

SQL> select count (*)
  2  from t
  3  where d =3D to_date('01-03-2001 = 16:34:59','mm-dd-yyyy hh24:mi:ss') ;

  COUNT(*)
----------
         = 0

SQL> select count (*)
  2  from t
  3  where to_char(d) =3D = to_char(to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss'))
  4  ;

  COUNT(*)
----------
         = 1

SQL> select count (*)
  2  from t
  3  where to_char(d,'mm-dd-yyyy = hh24:mi:ss') =3D '01-03-2001 16:34:59' ;

  COUNT(*)
----------
         = 1
select count (*)
from t
where d =3D to_date('01-03-2001 = 16:34:59','mm-dd-yyyy hh24:mi:ss') ;

Jacques R. Kilcho=EBr
(949) 754-8816
Quest Software, Inc.

8001 Irvine Center Drive
Irvine, California 92618
U.S.A.