Transformation: Date Time - Invalid Value - MySQL

[Michael_Litz]

Environment

• Qlik Replicate 
• Table/Field level transformation
• mySQL Source 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

 

Date Transformation

Some databases allow for non-standard date time values which when replicated to a target database that does not allow the same "Bad" value the task will error out.

An example of this is a replication task the has mySQl as the source and Snowflake as the target.
We have a mysql DB with few tables having datetime data as 0000-00-00 00:00:00 , when we are trying to load this data into snowflake we are running into below error.

 

Task Log Error

00010461: 2021-07-23T17:09:30 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized

Any way we can apply a global transformation on the data to change it from 0000-00-00 00:00:00
To 1970-01-01 00:00:00?

 

Global Transformation

Since there is no easy way to know if a table will always have good data values the best place to do this type of check and replace is on the Task's Global Transformation Screen. There is a global transformation type called Replace column value which we will use and it will apply to all date fields in all the tables of the task.

The complete transformation code as follows:

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)

 

Replace Column Value: screenshots

The following set of 4 screenshots show where and how to configure the Global Transformation Solution

Replace Column Value

When in the Global Transformation Screen select Replace column value

Transformation Scope

Select Data Type "DATETIME" to configure the task to apply this to all fields of type Date Time.

Transformation Action

Click on Create Expression to add the transformation code.

Expression Builder

Add the transformation code that checks for a "Bad" date value in this case zero's or empty string and then set the value to "1970-01-01 00:00:00" which is an acceptable date for Snowflake target.

NON Global Transformation

Non Global solution at the source DB - Table and field specific 

On the column within the table itself.
Here is what I've done for the column MyColumn in the table MyTable:

CASE
WHEN substr($MyColumn, 1, 4) = '0000'
THEN NULL
ELSE $MyColumn
END

[Prabodh]

What are the situations when Replicate writes 0000-00-00 00:00:00 as the value on target?

Is this a limitation in Replicate? If yes, when can we expect a permanent fix?

[harel_barash]

Hi Prabodh,

By default, Replicate applies the same value captured from the source. So if both source and target allow this value, it would be applied like this. If the target does not allow it, you can use the article above to overcome the conflict.

If you find another scenario where Replicate applies this value and the actual data in the source was not '0000-00-00 00:00:00', it requires investigation, so kindly open a support ticket.