https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/ta-p/1826202 <H4>Environment</H4> <UL> <LI><LI-PRODUCT title="Qlik Replicate" id="qlikReplicate"></LI-PRODUCT>&nbsp;</LI> <LI>Table/Field level transformation</LI> <LI>mySQL Source&nbsp;</LI> </UL> <P>&nbsp;</P> <P style="font-style: italic;">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.</P> <H3>&nbsp;</H3> <H3 id="toc-hId--525350720"><FONT color="#339966"><STRONG>Date Transformation</STRONG></FONT></H3> <P>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.</P> <P>An example of this is a replication task the has mySQl as the source and Snowflake as the target.<BR />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.</P> <H3>&nbsp;</H3> <H3 id="toc-hId--525350720"><FONT color="#339966"><STRONG>Task Log Error</STRONG></FONT></H3> <P>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</P> <P>Any way we can apply a global transformation on the data to change it from 0000-00-00 00:00:00<BR />To 1970-01-01 00:00:00?</P> <H3>&nbsp;</H3> <H3 id="toc-hId--525350720"><FONT color="#339966"><STRONG>Global Transformation</STRONG></FONT></H3> <P>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.</P> <P><STRONG>The complete transformation code as follows:</STRONG></P> <P>&nbsp;</P> <LI-CODE lang="markup">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)</LI-CODE> <P>&nbsp;</P> <H3>&nbsp;</H3> <P><STRONG>Replace Column Value: screenshots</STRONG></P> <P>The following set of 4 screenshots show where and how to configure the Global Transformation Solution</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Replace Column Value" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/59576iB0E0AC116927A9BC/image-size/large?v=v2&amp;px=999" role="button" title="Replcate column values.PNG" alt="Replace Column Value" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Replace Column Value</span></span></P> <P>&nbsp;</P> <P>When in the Global Transformation Screen select Replace column value</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Transformation Scope" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/59577i859B472D1F559BC4/image-size/large?v=v2&amp;px=999" role="button" title="Tarnsformation Scope.PNG" alt="Transformation Scope" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Transformation Scope</span></span></P> <P>&nbsp;</P> <P>Select Data Type "DATETIME" to configure the task to apply this to all fields of type Date Time.</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Transformation Action" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/59578iB4FC05F02CACC62A/image-size/large?v=v2&amp;px=999" role="button" title="Tarnsformation Action.PNG" alt="Transformation Action" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Transformation Action</span></span></P> <P>&nbsp;</P> <P>Click on Create Expression to add the transformation code.</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Expression Builder" style="width: 913px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/59579i88B0435E5B9A4FA3/image-size/large?v=v2&amp;px=999" role="button" title="Expression.PNG" alt="Expression Builder" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Expression Builder</span></span></P> <P>&nbsp;</P> <P>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.</P> <H3 id="toc-hId--525350720"><FONT color="#339966"><STRONG>NON Global Transformation</STRONG></FONT></H3> <P><STRONG>Non Global solution at the source DB - Table and field specific&nbsp;</STRONG></P> <P>On the column within the table itself. <BR />Here is what I've done for the column MyColumn in the table MyTable:</P> <P>CASE <BR />WHEN substr($MyColumn, 1, 4) = '0000' <BR />THEN NULL<BR />ELSE $MyColumn<BR />END</P> Thu, 30 Sep 2021 21:00:58 GMT Michael_Litz 2021-09-30T21:00:58Z https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/ta-p/1826202 <P>Replicate global transformation to detect and replace bad date values</P> Thu, 30 Sep 2021 21:00:58 GMT https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/ta-p/1826202 Michael_Litz 2021-09-30T21:00:58Z https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/tac-p/1833714#M4176 <P>What are the situations when Replicate writes&nbsp;<SPAN>0000-00-00 00:00:00 as the value on target?</SPAN></P><P><SPAN>Is this a limitation in Replicate? If yes, when can we expect a permanent fix?</SPAN></P> Fri, 03 Sep 2021 17:15:39 GMT https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/tac-p/1833714#M4176 Prabodh 2021-09-03T17:15:39Z https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/tac-p/1833957#M4188 <P>Hi Prabodh,</P><P>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.</P><P>If you find another scenario where Replicate applies this value and the actual data in the source was not '<SPAN>0000-00-00 00:00:00', it requires investigation, so kindly open a support ticket.</SPAN></P> Mon, 06 Sep 2021 11:14:48 GMT https://community.qlik.com/t5/Official-Support-Articles/Transformation-Date-Time-Invalid-Value-MySQL/tac-p/1833957#M4188 harel_barash 2021-09-06T11:14:48Z