two date columns comparison oracle do not work as expected

Question

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 ?

Regarding your edit "Is that reason I dont see my `hh:mi:ss`?" Yes, set the date format to `DD-MM-YYYY HH24:MI:SS` and you will see the date and time components. — MT0, Feb 27 '23 at 14:53

score 3 · Accepted Answer · answered Feb 27 '23 at 14:17

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)

why oracle does not show `hh:mi:ss` ? it's so misleading. how can i see this part of the date ? — moth, Feb 27 '23 at 14:19
This is a setting in the tool you are using to read the data. If you are using SQL Developer, see https://stackoverflow.com/a/8134646/2270762. — Thorsten Kettner, Feb 27 '23 at 14:21

score 2 · Answer 2 · answered Feb 27 '23 at 14:19

When you do:

SELECT *
FROM   mytable
WHERE  to_date(last_collected_date) < to_date(update_date);

Then TO_DATE expects a string as the first argument so your query is effectively:

SELECT *
FROM   mytable
WHERE  TO_DATE(
         TO_CHAR(
           last_collected_date,
           (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
         ),
         (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
       )
       <
       TO_DATE(
         TO_CHAR(
           update_date,
           (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
         ),
         (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
       );

If your NLS_DATE_FORMAT is DD-MON-RR (or equivalent without a time component) then what you are effectively doing is truncating the string back to midnight.

If that is what you want then use TRUNC:

SELECT *
FROM   mytable
WHERE  TRUNC(last_collected_date) < TRUNC(update_date);

However, it is not clear why last_collected_date < update_date would not work but why TRUNC(last_collected_date) < TRUNC(update_date) would work as if last_collected_date < update_date is false then TRUNC(last_collected_date) < TRUNC(update_date) must also be false.

Instead of `(SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')` you could also write simple `SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT')` - but maybe your version is more frightened and prevents users to do this famous mistake. — Wernfried Domscheit, Feb 27 '23 at 14:25

score 1 · Answer 3 · answered Feb 27 '23 at 14:10

1

Oracle dates store both date and time. So assuming the following data:

last_collected_date | update_date
2023-02-27 12:34:56 | 2023-02-27 23:00:00

Your first query would match to this data, because the last collected date is before the update date, despite both being on the same calendar day.

answered Feb 27 '23 at 14:10

Tim Biegeleisen

502,043
27
286
360

This would not be the cause as, assuming the `NLS_DATE_FORMAT` is `DD-MON-RR` (or equivalent) then `to_date(last_collected_date) < to_date(update_date)` will just truncate the dates to midnight and the comparison is `<` (not `<=`) so the rows would not match. – MT0 Feb 27 '23 at 14:14
ok and `to_date` sets `hh:mi:ss` to zero ? that's why it works ? I don't see the `hh:mi:ss` when querying my data. why ? – moth Feb 27 '23 at 14:14
i want to truncate everything to midnight. just want to get a day earlier. the funny thing is that i cannot even see the `hh:mi:ss` part when selecting the column – moth Feb 27 '23 at 14:16
@moth *ok and to_date sets hh:mi:ss to zero ?* No, `to_date()` takes a string parameter, so when you pass something different it first has to convert that value into a string in order to proceed. Converting a date to a string without specifying how makes it fall back on the session NLS defaults, which might strip the time details or it might not. – William Robertson Feb 27 '23 at 15:46

two date columns comparison oracle do not work as expected

3 Answers3