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.

Do a `SELECT SYSDATE FROM DUAL` and it will show you the issue. — Joe W, Aug 22 '14 at 15:28
This is what I tried, but SQL Developer had only DD-MON-RR for Date Format, and this is why I couldn't see the time. — ROMANIA_engineer, Aug 22 '14 at 15:35

score 10 · Accepted Answer · answered Aug 22 '14 at 15:26

10

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

answered Aug 22 '14 at 15:26

Lokesh

Thank you! I thought that my DATE doesn't contain time because my SQL Developer didn't show the whole format (only DD-MON-RR). I changed to DD-MON-RR HH:MI:SS and now it's very clear. (NLS > Date Format) – ROMANIA_engineer Aug 22 '14 at 15:34

1 Answers1