Cannot convert UTC to timestamp in Azure Data Factory

Question

Hi, i am not able to convert a UTC string value in a ADF cast transformation to a valid timestamp.
A Sample string value:
2022-09-27T11:45:11.2830000Z

The cast settings:

Data preview shows an error and the Timestamp column is NULL.

Any ideas?
Is there a type mismatch between the UTC and the timestamp format in ADF. I'm not sure, but are there 4 digits missing (only SSS instead of SSSSSSS)?
Even if i try to insert 4 additional SSSS in to the format string I receive the very same error.

Thanks a lot!

Answer 1

Hello @Markus Haunschmid ,

Thanks for the question and using MS Q&A platform.

As per my understanding your source data column has a timestamp value of type string and you would like to convert it into timestamp datatype column using Mapping data flow. When you are using Cast transformation for the format 'yyyy-MM-dd'T'hh:mm:ss.SSS'Z' you are seeing an error sign. Please correct if I'm not clear.

The reason for the error sign is that you are using a cast transformation with Assert type check enabled. Which means the cast transformation allows for type checking. If the casting fails, the row will be marked as an assertion error that you can trap later in the stream and type conversion errors always result in NULL and require explicitly error handling using an Assert transformation.

The issue here is your source format is 'yyyy-MM-dd'T'hh:mm:ss.SSSSSSS'Z' but in ADF the timeStamp format is support only until 3 digits of milliseconds i.e., 'yyyy-MM-dd'T'hh:mm:ss.SSS'Z' . Hence it is resulting in type conversion failure against your source data and marked as error and value is displayed as Null

To overcome this issue, you will have to handle this explicitly. For that you can use a derived column transformation instead of Cast transformation and use the below expression to convert your input datetime value string type to a timeStamp type.

toTimestamp(((left(TimeStampColumn, 23)) + 'Z'), 'yyyy-MM-dd\'T\'hh:mm:ss.SSS\'Z\'')  

Below is a sample for converting 'yyyy-MM-dd'T'hh:mm:ss.SSSSSSS'Z' to 'yyyy-MM-dd'T'hh:mm:ss.SSS'Z' format timeStamp.

Hope this will help.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how