Re: Help - newbie question on using date in SQL

From: Martin Doherty <martin.doherty_at_elcaro.moc>
Date: Thu, 14 Nov 2002 22:54:47 -0800
Message-ID: <KW0B9.18$8Q5.61@news.oracle.com>

The first one won't return rows because YY assumes the current century i.e. 2081. If you use RR instead of YY it will assume a year in the range 1950 - 2049, and you will get your rows.

My general advice is: DON'T USE TWO-DIGIT YEARS! Sheesh! Haven't we learnt anything?

Martin

andi wrote:

>Hi All,
>I'm using oracle 8.1.7.0 on win2K.
>I don't understand why the first sql statement doesn't return rows.
>The only difference between the two is the date of the second query
>is using 4 digits of year.
>
>Please give me some light on this.
>
>TIA.
>-Andi-
>
>
>SQL> select ename, hiredate from emp
> 2 where hiredate between to_date('20-FEB-81','DD-MON-YY') AND
>to_date('20-MAY-81','DD-MON-YY');
>
>no rows selected
>
>SQL> ed
>Wrote file afiedt.buf
>
> 1 select ename, hiredate from emp
> 2* where hiredate between to_date('20-FEB-1981','DD-MON-YYYY') AND
>to_date('20-MAY-1981','DD-MON-YYY')
>SQL> /
>
>ENAME HIREDATE
>---------- ---------
>ALLEN 20-FEB-81
>WARD 22-FEB-81
>JONES 02-APR-81
>BLAKE 01-MAY-81
>
>
>
>
Received on Fri Nov 15 2002 - 00:54:47 CST