How to calculate difference in date in snowflake?-CodePudding

I have these two days:

BEFORE_DATETIME: 2021-09-02 09:41:00
AFTER_DATETIME: 2021-09-09 09:41:00

I need to calculate the difference in these two days. So in this example: 7 days.However, in the BEFORE_DATETIME, I have some values that are string (bad records). I keep getting an error when I use DATEDIFF function because of those records.

How can I calculate the difference in date and to ignore the bad records?

CodePudding user response：

You can use

CodePudding user response：

If you use TRY_TO_DATE and the value "fails to parse" you will get null, thus you can feed the result of that TRY into the DATEDIFF or you can use an inline IFF you skip that thus something like:

IFF(TRY_TO_DATE(before_datetime) IS NOT NULL AND TRY_TO_DATE(after_datetime) IS NOT NULL, 
    DATEDIFF('days', before_datetime, after_datetime),
    0) AS alias_name

replacing 0 with what you want when the values are bad. etc