Re: Problem with query with dates in where clause

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Mon, 19 Jun 2000 01:23:38 -0700 (PDT)
Message-Id: <10533.109678@fatcity.com>

Shiva,

Your first query is going to check the full date including the time portion .

If the dates for hiredate stored in the table are truncated so that the time portion is 00:00:00 for all the records then you can use:

Select * from Emp
  where hiredate = trunc(sysdate);

If the dates contain time info, then use

Select * from Emp
  where trunc(hiredate) = trunc(sysdate);     Keep in mind that this query won't use an existing index on hiredate, unless you're on 8i and have created a function based index which truncates the date on the hiredate column.

For the second query, I suspect that your NLS_DATE_FORMAT is set to dd-mon-yy so you're actually looking for dates equal to 19-JUN-1900 instead of 2000

You can verify this by checking NLS_SESSION_PARAMETERS or alter the session before running the query: ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'; HTH,

• Anita
  • B Siva Shankar <bsshankar_at_chennai.tcs.co.in> wrote:
    >
    >
    > 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).
    

    ---

Do You Yahoo!? Received on Mon Jun 19 2000 - 03:23:38 CDT