SQL for a specific date in Oracle table

Question

I have a Oracle table with a DATE field, called UTC_DT. If I do the following query, I get proper results:

select *
from   t_table
where  trunc(UTC_DT) = to_date('20200406000000','YYYYMMDDHH24MISS');

But no result if I set some hour/min like in the following:

select *
from   t_table
where  trunc(UTC_DT) = to_date('20200406182000','YYYYMMDDHH24MISS');

Shouldn't this work as we convert both side to a date without time?

Answer 1

You seem confused between data types; perhaps you have worked in a different database environment in the past, with different data types for "dates" and "times".

Oracle's date data type has a misleading name - it should be called "date-time" because it always includes a time component. to_date() converts from string to date, and the result always has a time component. Even when you convert a string like '20200406' with format model 'yyyymmdd', with no time-of-day components; the default of '000000' for 'hh24miss' is added automatically. to_date does not truncate from "date-time" to "date" as you seem to expect (presumably without time component - which may exist in other database products, but not in Oracle).