Re: SQL question....

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/03/23
Message-ID: <953851535.19001.0.pluto.d4ee154e@news.demon.nl>#1/1

Your default date format is DD-MON-YY. Any date part not present in the literal is copied from the sysdate as it always has been. You are querying for 2081! You need to change your date format to dd-mon-rr or to include an explicit format everywhere.
The rr mask has been introduced several years ago.

Hth,

Sybrand Bakker, Oracle DBA

VS <slootsky_at_erols.com> wrote in message news:38DA933C.B16A1AE7_at_erols.com...
>
> Schema: SCOTT
> 8.0.5 or 8.1.5 environment
>
> WHY THIS GOING ON ?!!!!
>
> SQL> select ename, job,hiredate
> 2 from emp
> 3 where hiredate between
> 4 to_date ('20-FEB-81') AND
> 5 to_date ('01-MAY-81')
> 6 ORDER BY hiredate
> 7 /
>
> no rows selected
>
> SQL> select ename, job,hiredate
> 2 from emp
> 3 where hiredate between
> 4 to_date ('20-FEB-1981', 'DD-MM-YYYY') AND
> 5 to_date ('01-MAY-1981', 'DD-MM-YYYY')
> 6 ORDER BY hiredate
> 7 /
>
> ENAME JOB HIREDATE
> ---------- --------- ---------
> ALLEN SALESMAN 20-FEB-81
> WARD SALESMAN 22-FEB-81
> JONES MANAGER 02-APR-81
> BLAKE MANAGER 01-MAY-81
>
> SQL> spool off
>
>
Received on Thu Mar 23 2000 - 00:00:00 CST