Snowflake Failed to cast variant value “{TIMESTAMP VALUE}” to TIMESTAMP_NTZ

copy into DB.CANONICAL.ETAIL_TABLE(MAKE, SERIAL_NUMBER, DEVICE_KEY, CONFIG_TYPE, CSF, MESSAGE_TIMESTAMP)
from (select parse_json($1):header:make::Varchar(255),
    parse_json($1):header:serialNumber::Varchar(255),
    parse_json($1):header:deviceKey::Varchar(36),
    parse_json($1):header:configType::String, 
    parse_json($1), 
    parse_json($1):header:messageTimestamp::TIMESTAMP_NTZ(2)
from @DB.CANONICAL.ETAIL_STAGE/firehose/)
ON_ERROR = CONTINUE;

Above is the copy statement that I have made. But I keep getting the following error “Failed to cast variant value “2020-09-265T08:03:42.358Z” to TIMESTAMP_NTZ”

Answer

There’s a problem with the input data: September 265 is not a date.

If you want to parse that date to null, you can use TRY_CAST instead.

If you want to attempt to fix the date (September 26? September 25?), you could try going through a regex first.

SELECT '2020-09-265T08:03:42.358Z'::TIMESTAMP_NTZ;
# Timestamp '2020-09-265T08:03:42.358Z' is not recognized

SELECT '2020-09-26T08:03:42.358Z'::TIMESTAMP_NTZ;
# 2020-09-26 08:03:42.358

SELECT TRY_CAST('2020-09-265T08:03:42.358Z' AS TIMESTAMP_NTZ);
# null