Re: SQL question....

From: Paul Druker <pdruker_at_metaway.com>
Date: 2000/03/23
Message-ID: <n3xC4.6560$h3.41906@typhoon.southeast.rr.com>#1/1

This is correct result.

You are not providing 4-digit year in your first SQL statement, so Oracle takes century from the system date:

SQL> select sysdate from dual;

SYSDATE

---

23-MAR-00 SQL> select to_char(to_date ('20-FEB-81'),'DD-MON-YYYY') from dual;

TO_CHAR(TO_

---

20-FEB-2081 Basically, it means that in your first query you're looking for hiredate between 20-FEB-2081 and 01-MAY-2081 - that's why no rows.

Anyway, the best solution would be to use 4-digit year in your query:

SQL> select ename, job,hiredate

  2     from emp
  3     where hiredate between

  4 to_date ('20-FEB-1981') and
  5 to_date ('01-MAY-1981')
  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

Regards,
Paul

"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