Oracle sysdate is not equal to a stored date

Question

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.

• sysdate returns a DATE according to http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm

Answer 1

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);