How to calculate difference in date in snowflake?

You can use TRY_TO_TIMESTAMP function which will parse your input as a timestamp and returns NULL if parse fails.

Then you can apply your DATEDIFF as you please.

WITH cte AS (
  SELECT
     $1 AS before_datetime,
     $2 AS after_datetime
  FROM VALUES
  ('2021-09-02 09:41:00', '2021-09-09 09:41:00'),
  ('random_bad_record', '2021-09-09 09:41:00'),
  ('2021-09-02 09:41:00', 'random_bad_record')
)
SELECT
    TRY_TO_TIMESTAMP(before_datetime) as before_datetime,
    TRY_TO_TIMESTAMP(after_datetime) as after_datetime,
    DATEDIFF('days', TRY_TO_TIMESTAMP(before_datetime), TRY_TO_TIMESTAMP(after_datetime)) AS diff_in_days
FROM cte
;

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

I'd like to add to the discussion here about this point:

I have some values that are string (bad records).

I've seen date/time data that's inconsistent - pulled from various sources or typed in by a person in different formats. If you have "bad" dates like that, this SQL UDF comes in handy. It will try various common date and time formats before finally trying the base TRY_TO_TIMESTAMP. If none of them work it will return NULL.

It has a better chance at converting dates in string fields if they have inconsistent formats, but should only be used in that situation since it adds overhead to the conversion. You can see how the pattern works and add your own parser to the list of attempts.

create or replace function TRY_MULTI_TIMESTAMP(STR string)
returns timestamp
language SQL
as
$$
    case
        when STR RLIKE '[A-Za-z]{3} \\d{2} \\d{4} \\d{1,2}:\\d{2}:\\d{2}' then try_to_timestamp(left(STR, 20), 'MON DD YYYY HH24:MI:SS')
        when STR RLIKE '\\d{1,4}-\\d{1,2}-\\d{2} \\d{1,2}:\\d{2}:\\d{2} [A|P][M]' then try_to_timestamp(STR, 'YYYY-MM-DD HH12:MI:SS AM')
        when STR RLIKE '\\d{1,2}/\\d{1,2}/\\d{4}' then try_to_timestamp(STR, 'mm/dd/yyyy')
        when STR RLIKE '\\d{1,2}\\/\\d{1,2}\\/\\d{4} \\d{1,2}:\\d{2}:\\d{2} [A-Za-z]{2}' then try_to_timestamp(STR, 'MM/DD/YYYY HH12:MI:SS AM')
        when STR RLIKE '\\d{1,2}\\/\\d{1,2}\\/\\d{4} \\d{1,2}:\\d{2}' then try_to_timestamp(STR, 'MM/DD/YYYY HH24:MI')
        when STR RLIKE '[A-Za-z]{3}, \\d{1,2} [A-Za-z]{3} \\d{4} \\d{1,2}:\\d{1,2}:\\d{1,2} [A-Za-z]{3}' then try_to_timestamp(left(STR, len(STR) - 4) || ' ' || '00:00', 'DY, DD MON YYYY HH:MI:SS TZH:TZM')   -- From Snowflake "LIST" command
        when STR RLIKE '\\d{1,2}/\\d{1,2}/\\d{2} \\d{1,2}:\\d{2} [A|P][M]' then try_to_timestamp(STR, 'MM/DD/YY HH12:MI AM')
        when STR RLIKE '[A-Za-z]{3} [A-Za-z]{3} \\d{2} \\d{4} \\d{1,2}:\\d{2}:\\d{2} GMT.*' then try_to_timestamp(left(replace(substr(STR, 5), 'GMT', ''), 26), 'MON DD YYYY HH:MI:SS TZHTZM')  -- Javascript
        else try_to_timestamp(STR) -- Final try without format specifier.
    end
$$;

select try_multi_timestamp('Sat Oct 02 2021 17:53:40 GMT+0000 (Coordinated Universal Time)'); -- JavaScript date format
select try_multi_timestamp('Fri, 17 Apr 2020 17:55:45 GMT'); -- Snowflake LIST command file time
select try_multi_timestamp('Oct 26 2021 6:25:15');