Qlik Replicate and Snowflake Endpoint: Tables are suspended with Timestamp error

[narendersarva]

Qlik Replicate tasks error out, and tables are suspended.

RetCode: SQL_ERROR  SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00.000000' is not recognized Failed (retcode -1) to execute statement: 'INSERT  INTO "XXXXX_CTRL"."public_refer__ct"

Environment:

Qlik Replicate 
Snowflake Endpoint

Resolution:

'0000-00-00 00:00:00' is not a valid timestamp in Snowflake. A transformation will need to be added to change this to your desired value.

Here is an example, in this case we are replacing the timestamp to '1970-01-01 00:00:00':

coalesce(
CASE WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = '0000'
THEN '1970-01-01 00:00:00'
WHEN substr($AR_M_SOURCE_COLUMN_DATA,1,4) = ''
THEN '1970-01-01 00:00:00'
ELSE
$AR_M_SOURCE_COLUMN_DATA
END
,$AR_M_SOURCE_COLUMN_DATA)