Re: How to compare a date to sysdate

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

ramdan wrote:

> 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

Received on Tue Mar 02 1999 - 16:08:30 CST