How to compare a date to sysdate?

why does this not work?!!?!?

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-----------
02-mar-1999

SQL> select * from tab1row where readdate = (select sysdate from dual);

no rows selected

SQL> select * from tab1row where readdate = sysdate;

no rows selected

SQL> describe tab1row;
Name                            Null?    Type
------------------------------- -------- ----
NAME                                     VARCHAR2(10)
READDATE                                 DATE
READING1                                 NUMBER
READING2                                 NUMBER
READING3                                 NUMBER
READING4                                 NUMBER

SQL> select * from tab1row;

NAME       READDATE     READING1 READING2 READING3 READING4
---------- ----------- --------- --------- --------- ---------
TEST        04-mar-1999
system     04-mar-1999      1000      1500      2000      1400
system     04-mar-1999      1000      1500      2000      1400
test1        02-mar-1999

Ans1:
ramdan wrote
>SQL> SELECT SYSDATE FROM DUAL;

An Oracle data always includes the time as well.

As you do not specify a print format, Oracle prints it using the default
format which happend to be dd-mon-yyyy in your case. However, printing is only
done in the very final stage of your query. In the where clause, the dates
still have the time part, and you would be very lucky to have an exact match.

    select to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss')
    , to_char( trunc(sysdate), 'dd-Mon-yyyy hh24:mi:ss')
    from dual;

>select * from tab1row
>where readdate = (select sysdate from dual);

Like shown above, use trunc(my_date) to set the time part to midnight, 0:00:00

select *
from tab1row

where trunc(readdate) = trunc(sysdate);

Note that no subquery is needed to use sysdate.

Arjan.

Ans2:
This is because there is a time (mm:ss) in the sysdate and maybe also in
the readdate
try
select * from tab1row where readdate = trunc(sysdate) /* No need to select
from dual, sysdate is a built-in function, trunc will cut off the time */

or (worse)
select * from tab1row where trunc(readdate) = trunc(sysdate)
or (complicated, but it does work)
select * from tab1row where readdate between trunc(sysdate) and
trunc(sysdate) + 1/*day*/ -1/3600

Hth,

Sybrand Bakker, Oracle DBA

Hosted by www.Geocities.ws