| 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: Problem with query with dates in where clause
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_01BFDA04.9E5C80D0
Content-Type: text/plain;
charset="iso-8859-1"
I hate to be a stickler but, because BETWEEN is inclusive, your solution will include those transactions that occur exactly at midnight on 20-JUN-00. To be exact you should use the following.
WHERE hiredate BETWEEN TO_DATE('19-JUN-00') AND TO_DATE('19-JUN-00')+0.999988 Which selects everything until one second before midnight on the target date.
-----Original Message-----
From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com]
Sent: Monday, June 19, 2000 9:41 AM
To: Multiple recipients of list ORACLE-L
Subject: Re:Problem with query with dates in where clause
Shiva,
Yours is a VERY old problem with Oracle and dates. Basically an Oracle
data
field includes a time variable. In many cases this really stinks (like
yours).
What you need to do to keep the use of an index on this one is change the
where
clause to:
where hiredate between to_date('19-JUN-00') and to_date('20-JUN-00')
This will give you consistent results. Your original query gets the time
added
to it at whatever time it is when executed.
____________________Reply Separator____________________ Author: "B Siva Shankar" <bsshankar_at_chennai.tcs.co.in> Date: 6/19/00 12:24 AM
Hi List,
We have problems in queries which have dates in where clause.
Select * from Emp
where hiredate = sysdate;
Select * from Emp
where hiredate = '19-JUN-00';
The above two queries are not returning any records even though there are
some
records in the table satisfying the criteria.
Can someone explain why it is so. We don't want to use to_char while comparing.
Thanks in advance,
Shiva.
-- Author: B Siva Shankar INET: bsshankar_at_chennai.tcs.co.in Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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). -- Author: INET: dgoulet_at_vicr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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). ------_=_NextPart_001_01BFDA04.9E5C80D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printableReceived on Mon Jun 19 2000 - 10:28:05 CDT
<!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.2650.12">
<TITLE>RE: Problem with query with dates in where clause</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>I hate to be a stickler but, because BETWEEN is =
inclusive, your solution will include those transactions that occur = exactly at midnight on 20-JUN-00. To be exact you should use the = following.</FONT></P>
<P><FONT SIZE=3D2>WHERE hiredate BETWEEN TO_DATE('19-JUN-00') AND =
TO_DATE('19-JUN-00')+0.999988</FONT>
</P>
<P><FONT SIZE=3D2>Which selects everything until one second before =
midnight on the target date.</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: dgoulet_at_vicr.com [<A =
HREF=3D"mailto:dgoulet_at_vicr.com">mailto:dgoulet_at_vicr.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Monday, June 19, 2000 9:41 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re:Problem with query with dates in where =
clause</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Shiva,</FONT>
</P>
<P><FONT SIZE=3D2> Yours is a VERY old problem with =
Oracle and dates. Basically an Oracle data</FONT>
<BR><FONT SIZE=3D2>field includes a time variable. In many cases =
this really stinks (like yours). </FONT>
<BR><FONT SIZE=3D2>What you need to do to keep the use of an index on =
this one is change the where</FONT>
<BR><FONT SIZE=3D2>clause to:</FONT>
</P>
<P><FONT SIZE=3D2>where hiredate between to_date('19-JUN-00') and =
to_date('20-JUN-00')</FONT>
</P>
<P><FONT SIZE=3D2>This will give you consistent results. Your =
original query gets the time added</FONT>
<BR><FONT SIZE=3D2>to it at whatever time it is when executed.</FONT>
</P>
<P><FONT SIZE=3D2>____________________Reply =
Separator____________________</FONT>
<BR><FONT SIZE=3D2>Author: "B Siva Shankar" =
<bsshankar_at_chennai.tcs.co.in></FONT>
<BR><FONT SIZE=3D2>Date: 6/19/00 =
12:24 AM</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>Hi List,</FONT>
</P>
<P><FONT SIZE=3D2>We have problems in queries which have dates in where =
clause.</FONT>
</P>
<P><FONT SIZE=3D2>Select * from Emp</FONT>
<BR><FONT SIZE=3D2>where hiredate =3D sysdate;</FONT>
</P>
<P><FONT SIZE=3D2>Select * from Emp</FONT>
<BR><FONT SIZE=3D2>where hiredate =3D '19-JUN-00';</FONT>
</P>
<P><FONT SIZE=3D2>The above two queries are not returning any records =
even though there are some</FONT>
<BR><FONT SIZE=3D2>records in the table satisfying the criteria.</FONT>
</P>
<P><FONT SIZE=3D2>Can someone explain why it is so. We don't want to =
use to_char while comparing.</FONT>
</P>
<P><FONT SIZE=3D2>Thanks in advance,</FONT>
<BR><FONT SIZE=3D2>Shiva.</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: B Siva Shankar</FONT>
<BR><FONT SIZE=3D2> INET: bsshankar_at_chennai.tcs.co.in</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------= -----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
from). You may</FONT>
<BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: </FONT>
<BR><FONT SIZE=3D2> INET: dgoulet_at_vicr.com</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------= -----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
 |
 |