Re:Problem with query with dates in where clause

From: <dgoulet_at_vicr.com>
Date: Mon, 19 Jun 2000 09:29:19 -0400
Message-Id: <10533.109705@fatcity.com>

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____________________

Subject: Problem with query with dates in where clause 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).

Received on Mon Jun 19 2000 - 08:29:19 CDT