RE: Date comparison question

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 8 Jan 2001 12:04:02 -0800
Message-Id: <10735.126037@fatcity.com>

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_at_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(*)

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(*)

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2652.35">

<TITLE>RE: Date comparison question</TITLE>
</HEAD>
<BODY>

> -----Original Message----- > From: Lisa Yates [<A = HREF=3D"mailto:cosnit_at_creighton.edu">mailto:cosnit_at_creighton.edu</A>]</F=

ONT>
<BR><FONT SIZE=3D2>&gt; Sent: lundi, 8. janvier 2001 11:27</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Why doesn't this query ever return...</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; where run_date =3D to_date('01-03-2001 =

16:34:59','mm-dd-yyyy

<BR><FONT SIZE=3D2>&gt; hh24:mi:ss')</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; but this query does....</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; where to_char(run_date) =3D =

to_char(to_date('01-03-2001

<BR><FONT SIZE=3D2>&gt; 16:34:59','mm-dd-yyyy hh24:mi:ss'))</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; and so does this query....</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =

=3D '01-03-2001

<BR><FONT SIZE=3D2>&gt; 16:34:59'</FONT>
</P>
<BR>

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.

<P><FONT SIZE=3D2>SQL&gt; select count (*)</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; from t</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; where d =3D to_date('01-03-2001 =

16:34:59','mm-dd-yyyy hh24:mi:ss') ;

<P><FONT SIZE=3D2>&nbsp; COUNT(*)</FONT>
<BR><FONT SIZE=3D2>----------</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

0

<P><FONT SIZE=3D2>SQL&gt; select count (*)</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; from t</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; where to_char(d) =3D =

to_char(to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss'))   4  ;

<P><FONT SIZE=3D2>&nbsp; COUNT(*)</FONT>
<BR><FONT SIZE=3D2>----------</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

1

<P><FONT SIZE=3D2>SQL&gt; select count (*)</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; from t</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; where to_char(d,'mm-dd-yyyy =

hh24:mi:ss') =3D '01-03-2001 16:34:59' ;

<P><FONT SIZE=3D2>&nbsp; COUNT(*)</FONT>
<BR><FONT SIZE=3D2>----------</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

1

<BR><FONT SIZE=3D2>select count (*)</FONT>
<BR><FONT SIZE=3D2>from t</FONT>
<BR><FONT SIZE=3D2>where d =3D to_date('01-03-2001 =

16:34:59','mm-dd-yyyy hh24:mi:ss') ;

<P><FONT SIZE=3D2>Jacques R. Kilcho=EBr</FONT>
<BR><FONT SIZE=3D2>(949) 754-8816</FONT>
<BR><FONT SIZE=3D2>Quest Software, Inc.</FONT>
</P>

<P><FONT SIZE=3D2>8001 Irvine Center Drive</FONT>

Irvine, California 92618 U.S.A. Received on Mon Jan 08 2001 - 14:04:02 CST