Oracle - Date comparison with SYSDATE

Question

I need to compare date with date of current day, using SYSDATE, something like this:

SELECT * FROM my_table
WHERE date_column BETWEEN TO_DATE(SYSDATE -3,'dd.mm.yyyy') AND TO_DATE(SYSDATE,'dd.mm.yyyy');

However, this produces no result....My question :

Based on accepted answer here we should NEVER EVER compare strings with date. But in other side, a SYSDATE is allready a Date data type, and we should not compare It to a date - see here.

If I replace TO_DATE with TO_CHAR in upper SQL things go working again. But TO_CHAR function converts into String, so Oracle (I pressume) needs to convert this string again to date so you force Oracle to do an implicit data type conversion.

So, what should be a correct comparison with date and SYSDATE, in order to avoid Oracle working a bit slowly ?

NEVER, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with. — a_horse_with_no_name
You misinterpreted the second link. And as @a_horse_with_no_name wrote and both answers suggested, avoid type conversion functions (e.g. TO_DATE, TO_CHAR) when the type is already correct, as you will see strange results, coming from the fact that in such cases you actually get two type conversions and you control only one. All in all, to answer your question, we'd need to know, what is the actual type used for the date_column? — Hilarion

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-11-22T07:03:47

You should not need to call either TO_DATE or TO_CHAR:

SELECT *
FROM my_table
WHERE date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE + 1);

Assuming date_column is a date type, you should be able to directly compare it against SYSDATE, or SYSDATE offset by some number of days.

Oracle - Date comparison with SYSDATE

1 Answers