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?
CodePudding user response:
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).