CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
lalaland
lalaland

Reputation: 341

How to use DATEADD in a case statement in snowflake?

I have a table with a column PERIOD_DAYS that is in varchar but contains a lot of numbers. I have another column called FIRST_DATETIME, which is in datetime data type. I want to add the numbers in the PERIOD_DAYS to the FIRST_DATETIME to get the FINAL datetime. So if datetime = '2020-01-01' + 2 DAYS, the final_datetime should be = '2020-01-03'

I have a query that contains a case statement, that says if the first_datetime IS NULL, then default the final_datetime to 0. However, I keep getting an error.

This works:

select
ID,
PERIOD_DAYS,
FIRST_DATETIME,
DATEADD('days',try_to_number(PERIOD_DAYS), FIRST_DATETIME) as FINAL_DATETIME
from TBL_A a
group by ID,PERIOD_DAYS,FINAL_DATETIME

Does not work:

select
ID,
PERIOD_DAYS,
FIRST_DATETIME,
case when try_to_number(PERIOD_DAYS) IS NULL then 0 else DATEADD('days',try_to_number(PERIOD_DAYS), FIRST_DATETIME) END as FINAL_DATETIME
from TBL_A a
group by ID,PERIOD_DAYS,FINAL_DATETIME

I need the second query to work to take into account the non-numeric values in period_days columns.

Upvotes: 0

Views: 1116

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26043

The problem as you have it is because you CASE is returning 0 which is a NUMBER on one branch and TIMESTAMP_NTZ on the other.

These are not compatible types. I would recommend using NULL instead of zero.

thus:

CASE WHEN try_to_number(PERIOD_DAYS) IS NULL THEN null 
     ELSE DATEADD('days',try_to_number(PERIOD_DAYS), FIRST_DATETIME) 
END as FINAL_DATETIME

Given this only has 2 branches a IFF can be used instead:

IFF(try_to_number(PERIOD_DAYS) IS NULL, null, DATEADD('days',try_to_number(PERIOD_DAYS), FIRST_DATETIME) ) as FINAL_DATETIME

Upvotes: 1

Related Questions