I am trying to implement below statement in Snowflake view
IFF("Closed" = TRUE,DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date()),FALSE) - get True/False
and it wont workif I giveIFF("Closed" = TRUE,'1234',FALSE) - this works and i get 1234 or False based on value of closed
can someone please help me why DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date()) is not giving result within IFFI tested the statement is correct and right value if i execute it separately.
Best Answer
It is all about data types
CASE
In the second form of CASE, each value is a potential match for expr. The value can be a literal or an expression. The value must be the same data type as the expr, or must be a data type that can be cast to the data type of the expr.
The difference is as follow:
DATEDIFF returns INTEGER, BOOLEANvs'1234' string literal, BOOLEANAnd for Booelan it works as follow:
TO_BOOLEAN:
For a text expression, string must be:'true', 't', 'yes', 'y', 'on', '1' return TRUE.'false', 'f', 'no', 'n', 'off', '0' return FALSE.All other strings return an error.Strings are case-insensitive.For a numeric expression:0 returns FALSE.All non-zero values return TRUE.
EDIT:
In order to return string instead of boolean explicit cast could be used:
IFF("Closed" = TRUE,TO_VARCHAR(DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date())),FALSE)-- "Closed" column is probably boolean so there is no need to ` = TRUE`:IFF("Closed",TO_VARCHAR(DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date())),FALSE)