1

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?

x89
  • 2,798
  • 5
  • 46
  • 110
  • NULLIF takes two arguments that must be the same type. The string literal 'null' is not the same type as the first argument (timestamp_ntz). Your first example works because both arguments are the same type (string). – TJ Murphy Feb 21 '22 at 20:46

1 Answers1

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)
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275