oracle sql date comparison doesn't work as expected

oracle, oracle-sqldeveloper, select, sql

Solution

Oracle `DATE` columns contain a time as well (despite their name). Your existing rows probably have a time different than `00:00:00` (which is "assigned" to the date you create with the `to_date()` function).

You need to "remove" the time part of the column using `trunc()`

AND NOT (trunc(X.INSERT_DATE) = to_date('2013-01-17', 'yyyy-mm-dd'))

although I'd prefer to use `<>` instead of the `NOT` operator:

AND (trunc(X.INSERT_DATE) <> to_date('2013-01-17', 'yyyy-mm-dd'))

(but that is just a personal preference. I think it makes the condition easier to read).

So your complete statement would be:

SELECT insert_date
FROM X
  WHERE trunc(X.INSERT_DATE) <> to_date('2013-01-17', 'yyyy-mm-dd')

Problem

I have a table X with 'insert_date' column. This column is od type DATE and contains only one value for all records: "17-JAN-13". I would expect that following query return no results at all: ``` SELECT insert_date FROM X WHERE ("X"."INSERT_DATE" IS NOT NULL AND NOT (("X"."INSERT_DATE" = to_date('2013-01-17', 'yyyy-mm-dd') ))) ``` But what I'm getting instead is many "17-JAN-13" records. What's wrong with my query?

Original source