Search code examples
sqloraclewhere-clauseprecisionsysdate

Oracle sysdate is not equal to a stored date

Database: Oracle

Simplified version:

Table T has a column: D ( DATE type )

Now the table is empty.

INSERT INTO T
    (D) VALUES (sysdate);

Now the table has one row containing 22-AUG-14.

Why is the following WHERE clause false ?

SELECT * FROM T
    WHERE D = sysdate;

=> 0 rows

The following query works:

SELECT * FROM T
    WHERE TO_CHAR(D, 'DD/MM/YYYY') = TO_CHAR(sysdate, 'DD/MM/YYYY');

=> 1 row

I suppose that there is a "lost of precision" related to time (similar to double & int), but why is it possible ? Because both types are DATE.

Solution

  • sysdate also includes time, so this query will always fail to return any rows as time will keep changing 

    SELECT * FROM T
    WHERE D = sysdate;

    Try this 

    SELECT * FROM T
    WHERE trunc(D) = trunc(sysdate);