RE: Problem with query with dates in where clause

Message-Id: <10533.109729@fatcity.com> From: dgoulet@vicr.com Date: Mon, 19 Jun 2000 13:13:13 -0400 Subject: 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" 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" 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@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@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@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@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). RE: Problem with query with dates in where clause

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@vicr.com [mailto:dgoulet@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@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@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@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@vicr.com