check for 'null' timestamp values

Question

I wanted to check if a field has a "null" value. If so, I wanted to replace the "null" with a NULL value. This works for normal fields:

 SELECT NULLIF(JSON_DATA:"TO_Creator"::string, 'null') as "TO_CREATOR" FROM TABLE

However, it doesn't work with timestamps :

NULLIF(JSON_DATA:"TO_Collected_Date_Time"::timestamp_ntz, 'null') as "TO_COLLECTED_DATE_TIME",

This would later throw errors that:

SQL Error [100035] [22007]: Timestamp 'null' is not recognized

How can I check the same thing for NULL values within the timestamp field too?

Answer 1

In this scenario TRY_TO_TIMESTAMP_NTZ should suffice:

A special version of TO_TIMESTAMP / TO_TIMESTAMP_* that performs the same operation (i.e. converts an input expression into a timestamp), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error)

SELECT TRY_TO_TIMESTAMP_NTZ(JSON_DATA:"TO_Collected_Date_Time"::STRING)