CODEWITHSUNDEEP
oracle-databasedate
Lucy82
Lucy82

Reputation: 693

Oracle - Date comparison with SYSDATE

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 ?

Upvotes: 1

Views: 18304

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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.

Upvotes: 2

Related Questions