Mapping data flow format date exception

Vaibhav Palve 1 Reputation point
2021-10-02T07:20:47.763+00:00

In Data factory mapping data flow, I am looking for replacement of TRY_CAST() in SQL server.

I am using ToDate() function to cast my date, however, the column also has junk data in between which cannot be converted to date.

I want to cast to date, only if matches the given date format, otherwise insert/make it NULL.

For example, try exception handling for the date format.

ToDate('2020-01-01') -> '2020-01-01' should give results

ToDate('63716363') -> Null

I am getting Arithmetic data flow error converting timestamp to date.

How to resolve this issue ?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,083 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee
    2021-10-04T06:50:04.763+00:00

    Hi @Vaibhav Palve ,

    Welcome to Microsoft Q&A Platform. Thank you for posting query here.

    We can achieve your requirement using iif() function.

    Use below expression, to try to type cast value to date. If dataflow unable to type cast junk data then it will return null. Hence here, I am trying to see if toDate() function giving null or not and based on that trying to load correct date or empty value.
    So, in my output all correct values will stay and junk values gets replace by empty.

    137277-image.png

    In sink transformation you can write post query to replace all empty values with null in target table.

    137297-image.png

    Hope this will help. Please let us know if any further queries. Thank you.

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

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments