Official Support Articles

[Dineshan]

A task which has been running okay for months started to build latency. 

Error:

22007 NativeError: 100040 Message: Date '0000-00-00' is not recognized

Resolution

This was due to a wrong date. We found an expression for the the bad date from the Source. 

Steps to resolve the issue:

1. Open the target table from Replicate GUI
2. Select "Transform" from the left menu
3. For all the dates you see on the right side click on the "fx" and paste the below expression on the expression builder.
   
   
   
   
4. Paste the below expression in Build Expression window on the right side.
   

   CASE
   WHEN strftime('%Y-%m-%d', $ModifiedDate) IS NULL THEN
   '0001-01-01'
   ELSE
   $ModifiedDate
   END​

   
   

5. You can test it by clicking on parse expression and test expression. If the value, you entered is a valid date then the output should be the valid date. If the value, you entered is null you should see '0001-01-01' like above.

Environment

Qlik Replicate