| 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:RE: Problem with query with dates in where clause
Well, I suppose, if you want to be that stuck up about it!! :-)
____________________Reply Separator____________________Subject: RE: Problem with query with dates in where clause Author: "Shockey; David" <DShockey_at_jwrinc.com> Date: 6/19/00 8:46 AM
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-----
Sent: Monday, June 19, 2000 9:41 AM
To: Multiple recipients of list ORACLE-L
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).Received on Mon Jun 19 2000 - 12:13:13 CDT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2650.12">
<TITLE>RE: Problem with query with dates in where clause</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>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=2>WHERE hiredate BETWEEN TO_DATE('19-JUN-00') AND
TO_DATE('19-JUN-00')+0.999988</FONT>
</P>
<P><FONT SIZE=2>Which selects everything until one second before midnight on the
target date.</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: dgoulet_at_vicr.com [<A
HREF="mailto:dgoulet_at_vicr.com">mailto:dgoulet_at_vicr.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Monday, June 19, 2000 9:41 AM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: Re:Problem with query with dates in where
clause</FONT>
</P>
<BR>
<P><FONT SIZE=2>Shiva,</FONT>
</P>
<P><FONT SIZE=2> Yours is a VERY old problem with Oracle and
dates. Basically an Oracle data</FONT>
<BR><FONT SIZE=2>field includes a time variable. In many cases this really
stinks (like yours). </FONT>
<BR><FONT SIZE=2>What you need to do to keep the use of an index on this one is
change the where</FONT>
<BR><FONT SIZE=2>clause to:</FONT>
</P>
<P><FONT SIZE=2>where hiredate between to_date('19-JUN-00') and
to_date('20-JUN-00')</FONT>
</P>
<P><FONT SIZE=2>This will give you consistent results. Your original query
gets the time added</FONT>
<BR><FONT SIZE=2>to it at whatever time it is when executed.</FONT>
</P>
<P><FONT SIZE=2>____________________Reply Separator____________________</FONT>
<BR><FONT SIZE=2>Author: "B Siva Shankar"
<bsshankar_at_chennai.tcs.co.in></FONT>
<BR><FONT SIZE=2>Date: 6/19/00 12:24
AM</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=2>Hi List,</FONT>
</P>
<P><FONT SIZE=2>We have problems in queries which have dates in where
clause.</FONT>
</P>
<P><FONT SIZE=2>Select * from Emp</FONT>
<BR><FONT SIZE=2>where hiredate = sysdate;</FONT>
</P>
<P><FONT SIZE=2>Select * from Emp</FONT>
<BR><FONT SIZE=2>where hiredate = '19-JUN-00';</FONT>
</P>
<P><FONT SIZE=2>The above two queries are not returning any records even though
there are some</FONT>
<BR><FONT SIZE=2>records in the table satisfying the criteria.</FONT>
</P>
<P><FONT SIZE=2>Can someone explain why it is so. We don't want to use to_char
while comparing.</FONT>
</P>
<P><FONT SIZE=2>Thanks in advance,</FONT>
<BR><FONT SIZE=2>Shiva.</FONT>
</P>
<BR>
<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: B Siva Shankar</FONT>
<BR><FONT SIZE=2> INET: bsshankar_at_chennai.tcs.co.in</FONT>
</P>
<P><FONT SIZE=2>Fat City Network Services -- (858)
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2>San Diego, California
-- Public Internet access / Mailing Lists</FONT>
<BR><FONT
SIZE=2>--------------------------------------------------------------------</FON T>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail
message</FONT>
<BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
and in</FONT>
<BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB
ORACLE-L</FONT>
<BR><FONT SIZE=2>(or the name of mailing list you want to be removed
from). You may</FONT>
<BR><FONT SIZE=2>also send the HELP command for other information (like
subscribing).</FONT>
<BR><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: </FONT>
<BR><FONT SIZE=2> INET: dgoulet_at_vicr.com</FONT>
</P>
<P><FONT SIZE=2>Fat City Network Services -- (858)
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2>San Diego, California
-- Public Internet access / Mailing Lists</FONT>
<BR><FONT
SIZE=2>--------------------------------------------------------------------</FON T>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail
message</FONT>
<BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
and in</FONT>
<BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB
ORACLE-L</FONT>
 |
 |