I´m trying to change a specific date to a other one in order to compare the hash values of two queries. For that I´m using the CASE statement. But I can´t understand why I´m getting the following error
The column DATE_VALUE has the data type DATE and I´m operating on a Snowflake Database
inconsistent data type for result columns for set operator input branches, expected DATE, got BOOLEAN for expression [{2}] branch {3} SQL statement
SELECT COL , CASE WHEN DATE_VALUE = '2021-01-10' THEN DATE_VALUE = CAST('2021-01-11' AS DATE) END AS DATE_VALUE ,HASH( CASE WHEN DATE_VALUE = '2021-01-10'::DATE THEN DATE_VALUE= '2021-01-11'::DATE END , COL , COL , COL , COL , COL , COL , COL ) FROM TABLE1 MINUS SELECT COL , DATE_VALUE , HASH( DATE_VALUE , COL , COL , COL , COL , COL , COL , COL ) FROM TABLE2; 2 Answers
You have this expression for the second column:
(CASE WHEN DATE_VALUE = '2021-01-10' THEN DATE_VALUE = CAST('2021-01-11' AS DATE) END) AS DATE_VALUE The THEN clause is:
DATE_VALUE = CAST('2021-01-11' AS DATE) This is a boolean expression, not a date expression. You seem to want:
(CASE WHEN DATE_VALUE = '2021-01-10' THEN CAST('2021-01-11' AS DATE) END) AS DATE_VALUE Or:
(CASE WHEN DATE_VALUE = '2021-01-10' THEN '2021-01-11'::DATE END) AS DATE_VALUE CASE..WHEN is incorrect.
It should be
CASE WHEN DATE_VALUE = '2021-01-10' THEN CAST('2021-01-11' AS DATE) -- DATE_VALUE = CAST('2021-01-11' AS DATE) returns boolean not date END AS DATE_VALUE 
