I have a query that involves a where clause which i compare two columns that have DATE as their data type. these are last_collected_date and update_date.
SELECT *
FROM mytable
WHERE last_collected_date < update_date;
The weird thing is that this does not work as expected and I get dates of last_collected_date that have the same day as update_date.
However when I use to_date function:
SELECT *
FROM mytable
WHERE to_date(last_collected_date) < to_date(update_date);
It works as expected. Why is that ? Does oracle convert dates into strings internally in a select statement ?
This is my NLS settings. Is that reason I dont see my hh:mi:ss ?
>Solution :
DATE in Oracle is a misnomer; it is a datetime really. This means it always consists of a date and a time, and if we want to look at dates only, we set the time to midnight.
With
WHERE last_collected_date < update_date
you may be comparing 2023-02-27 09:00 with 2023-02-27 11:00. Same date, but the WHERE clause results in true, because of the different times.
This:
WHERE to_date(last_collected_date) < to_date(update_date)
is kind of dangerous, because of hidden implicit conversions, and only happens to work for you coincidentally. TO_DATE is a function applied on a string you want to convert to a datetime. What Oracle does is convert your datetimes to strings according to your session settings and then back to datetimes. Your session setting seems to be date-only, so you happen to lose the time part in the process and set your two dates to midnight thus.
If you want to compare dates, truncate the datetime to midnight:
WHERE last_collected_date < TRUNC(update_date)