1

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 ?

Lucy82
  • 654
  • 2
  • 12
  • 32
  • 4
    **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. –  Nov 22 '18 at 07:06
  • 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 Nov 22 '18 at 07:10

1 Answers1

2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    The `TO_DATE` use had probably a side effect, that @Lucy82 may wanted, i.e. the time truncation, so the statement should probably be `date_column BETWEEN TRUNC(SYSDATE - 3) AND SYSDATE` or even `date_column >= TRUNC(SYSDATE - 3) AND date_column < TRUNC(SYSDATE+1)` – Hilarion Nov 22 '18 at 07:07
  • @Hilarion I agree strongly with your second query. – Tim Biegeleisen Nov 22 '18 at 07:10
  • Thanks both of you, It makes sense to me now. But what is more appropriate, using TRUNC or without ? I need to optimize in terms of speed. – Lucy82 Nov 22 '18 at 07:12
  • @Lucy82 It depends on what you want your query to do. If you want to restrict `date_column` to the date three days earlier up to and including today, then use my answer. – Tim Biegeleisen Nov 22 '18 at 07:15
  • @TimBiegeleisen, I need this for my BULK Inserts. I have a procedure which fills up tables with lots of data, and all queries contain date comparisons. So I want to minimize Oracle struggling with date comparison to zero :) – Lucy82 Nov 22 '18 at 07:17
  • 1
    The query logic and the optimization are largely two different things. For example, if you wanted my query to run fast, you might look into adding indices to your table. – Tim Biegeleisen Nov 22 '18 at 07:19
  • @TimBiegeleisen, that is unfortunatelly something I can't do, I haven't got all priviliges in DB. Beside that, everything that could be done to optimize tables was allready done. Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow. – Lucy82 Nov 22 '18 at 07:23
  • @TimBiegeleisen, anyway, thanks for quick response. I'll test both suggestions and see what suits me better. – Lucy82 Nov 22 '18 at 07:24
  • With or without `TRUNC` on `SYSDATE`, the query would behave practically the same from the performance point of view, but may return different results (that depends on the actual data). You are just applying the function once in the query execution, not per each row (unless you apply it to columns). For indices, you need to have index creation privileges on this particular table and an index created on the `date_column`. This may however not be a full solution, as you probably haven't shown us the real query, and the real one is probably more complex, which may prevent index usage anyway. – Hilarion Nov 22 '18 at 07:40
  • As for "everything that could be done to optimize tables was allready done", this can't be true, as there's no such thing as optimizing tables. You optimize the DB for particular use (i.e. what kind of queries, inserts, updates, deletes you do), and depending on the particular use, **removing** indices may improve performance. As you are changing your queries just now, you should be also reviewing your DB for optimization changes too. "Optimizing" the DB without taking the queries into consideration, may have the opposite results. – Hilarion Nov 22 '18 at 07:47
  • 1
    @Hilarion A simple index on `date_column` might work, if that index also covered the other columns included with `SELECT *`. – Tim Biegeleisen Nov 22 '18 at 07:52
  • 1
    *" Problem is that all inserts are done by selecting & joining tables with over 500 millions or rows, so queries run slow"* - truncating `sysdate` won't cause performance problems. What you need to tackle is one or more of: a suboptimal query (such as join choices), stale statistics, wrong indexing strategy or a poor data model. – APC Nov 22 '18 at 08:12