Re: Date Comparison

From: Stephan Witt <witt_at_beusen.de>
Date: 1996/12/13
Message-ID: <32B1A231.2781E494@beusen.de>#1/1

Steve Dirschel wrote:
>
> Could someone please explain this to me:
>
> Create table dummy (a date);
>
> Insert into dummy values (sysdate);
> Insert into dummy values (sysdate);
> Commit;
>
> Select * from dummy;
> 10-DEC-96
> 10-DEC-96
>
> Select * from dummy where a = '10-DEC-96'
>
> 0 rows selected
>
> Select * from dummy where a > '10-DEC-96'
> 10-DEC-96
> 10-DEC-96
> 2 rows selected
>
> Why do I not get 2 rows returned when I try the select * from dummy
> where a = '10-DEC-96' ? I realize I can do a "less than 11-DEC-96 and
> greater than 10-DEC-96" but it seems like I shouldn't have to do all
> of that typing.
>
> Thanks in advance

Steve,

the date column stores date+time.
Your insert is as precise as seconds, your where clause gets converted to day.
Some examples:

SQL> select VALUE
from nls_session_parameters
where PARAMETER = upper('nls_date_format');

VALUE

---

DD.MM.YY

SQL> select sysdate, to_date ('13.12.95') from dual;

SYSDATE TO_DATE(
-------- --------
13.12.96 13.12.95

SQL> alter session set nls_date_format = 'HH24:MI:SS, DD.MM.YYYY';

Sitzung wurde geändert.

SQL> select sysdate from dual;

SYSDATE

---

19:26:01, 13.12.1996

What you're looking for is:

SQL> Select * from dummy where trunc(a) = '10-DEC-96' ;

But beware! You're loosing your indexed access path with that expression.
If your table is really big (>1000 records) modify the query, spend some typing and do it this way:

SQL> Select * from dummy
where a between trunc(to_date('10-DEC-96')) and trunc(to_date('11-DEC-96'))-1/84600 ;

Or more convenient:

SQL> Select * from dummy
where a between trunc(to_date('10-DEC-96')) and trunc(to_date('10-DEC-96'))+1-1/84600 ;

Hope it helps, Stephan

---

<stephan.witt_at_beusen.de>  | "beusen" Software+Systeme GmbH
fon: +49 30 549932-62     | Landsberger Allee 392
fax: +49 30 549932-21     | 12681 Berlin, Germany
---------------------------------------------------------------

Received on Fri Dec 13 1996 - 00:00:00 CST