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!
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')