cancel
Showing results for 
Search instead for 
Did you mean: 

Snowflake SCD2 - malformed date issue

saikumar1273
New Contributor

Hi Team,
Can anyone please help me in the following issue which I'm facing with "Snowflake - SCD2" snap.

As per my requirement, I have to load the timestamp data into the table (we are receiving the input date data in 12hour format). In the Snowflake target created a field with the data type TIMESTAMP_NTZ(9) to hold this data.

The sample source data (receiving in text file) what we are receiving for this field looks like this: "3/2/2021 2:10:51 PM" or "3/11/2021 9:13:30 AM"

saikumar1273_0-1711713031833.png

I'm using the following derivation, to convert the source data into 24hour timestamp format.
Date.parse(($field007 == '' ? null : $field007),'MM/dd/yyyy hh:mm:ss a').toLocaleDateTimeString({format: "yyyy-MM-dd HH:mm:ss"})

Output of the derivation:

saikumar1273_1-1711713105384.png

As per requirement, I have to implement SCD Type2 for this process and we are expecting the change in this timestamp field, so included in this field in the Cause-historization section of Snowflake SCD2 snap.

For Initial load the data loaded properly. For the incremental run, I'm getting the following error in the Snowflake SCD2 snap

Failure: Failed to convert historization field input value to required type, Reason: Invalid format: "2021-03-11 09:13:30" is malformed at " 09:13:30", Resolution: Please address the reported issue.

saikumar1273_3-1711713300137.png

Can anyone please help me resolving the issue which I'm facing?
Thanks in advance.

0 REPLIES 0