0

I have the following condition:

WHERE ORDER_DATE >= TO_DATE (CURRENT_DATE, 'YYYY-MM-DD')

The left part (ORDER_DATE) is stored as a VARCHAR and the right part is a DATE. When I run the code I have no error; meaning that the comparison between a VARCHAR and a DATE is working. I feel like the VARCHAR is converted in a DATE and then the comparison operator is applied; when I have '0000-00-00' data in the ORDER_DATE it returns a format error (data exception - invalid value for YYYY format token; Value: '0000-00-00'). Can someone confirm that my hypothesis is right?

*Yes I know I should store or convert my VARCHAR in a DATE and then perform my comparison between two DATE and this is what I have done. However, I noticed this particularity and I would like to understand the reason behind!

savag
  • 3
  • 2
  • I don't know how the varchar date is generated but that should be consistent. May be you need to datafix those different strings. Did you try CAST(ORDER_DATE AS DATE)? – Ashish Jain Aug 18 '21 at 21:27

1 Answers1

0

You have multiple strange things going on. In terms of conversion:

WHERE ORDER_DATE >= TO_DATE(CURRENT_DATE, 'YYYY-MM-DD')
----------------------------^ CURRENT_DATE is converted to a string because TO_DATE() expects a string as the first argument
--------------------^ TO_DATE then converts the string to a date using the specified format
-----------------^ The comparison converts the ORDER_DATE to a date (and you might get a type conversion error).

Note: This is based on the rules of Standard SQL. I assume that Exasol follows these rules (which are consistent across databases).

Presumably, your ORDER_DATE is in the format YYYY-MM-DD. If so, it is comparison safe and you can use:

ORDER_DATE >= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes my `ORDER_DATE` is in format YYYY-MM-DD so the comparison of two `VARCHAR` is safe. However, in this case, is it a better practice to convert the `ORDER_DATE` in a `DATE` and apply the comparison on two `DATE` instead? Example: ```WHERE CAST(ORDER_DATE AS DATE >= CURRENT_DATE``` – savag Aug 19 '21 at 18:28
  • @savag . . . No. The correct practice is to stored `ORDER_DATE` as a string. Period. If you are stuck with `ORDER_DATE` as a string in YYYY-MM-DD format, then string comparisons will usually be faster than converting to a date. – Gordon Linoff Aug 19 '21 at 21:27