| 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 is a multi-part message in MIME format.
--------------078774F956C130798F1C9339 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
Shiva,
Had to do this a lot in a former role. Something like this may help.
set echo off verify off
accept sdt prompt 'Enter Starting Date (dd/mm/yyyy) ==>> ' accept edt prompt 'Enter Ending Date (dd/mm/yyyy) ==>> '
select emp_id
from emp
where hire_dt >= to_date('&sdt'||' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and hire_dt <= to_date('&edt'||' 23:59:59','dd/mm/yyyy hh24:mi:ss')
and sackemnowflag = 'Y';
HTH, Cheers,
Casey ...
dgoulet_at_vicr.com wrote:
> 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).
--------------078774F956C130798F1C9339
Content-Type: text/x-vcard; charset=us-ascii;
name="cdyke.vcf"
Content-Transfer-Encoding: 7bit Content-Description: Card for Casey Dyke Content-Disposition: attachment;
begin:vcard
n:Dyke;Casey
tel;pager:(022) 9436 9290 tel;home:61 2 9948 1487 tel;work:61 2 9005 1021
url:www.realsurf.com org:At Home Network Australia;IT-Ops adr:;;100 Harris Street;Pyrmont;NSW;2042;Australiaversion:2.1
 |
 |