Converting string to date is failing

Question

Hello,
I have incoming files with name such as
202203010764_abcd.csv
2022030598764_abcd.csv
202203123454_abcd.csv
20220322837664_abcd.csv

I have created a new field in my copy activity "Filedate" as @substring(item().name, 0, 8)
in my Dataflow, i have a new column NewFileDate with the expression
coalesce(toDate(Filedate,'MM/dd/yyyy'),toDate(Filedate,'yyyy/MM/dd'),toDate(Filedate,'dd/MM/yyyy'),toDate(Filedate,'MMddyyyy'),toDate(Filedate,'yyyyddMM'),toDate(Filedate ,'MMddyyyy'),toDate(Filedate ,'yyyyMMdd'))

I dont get the results correctly always. , so for the above, i get
2022-01-03
2022-05-03
2022-12-03
2022-03-28

What am i missing ?

Answer 1

coalesce(toDate(Filedate,'MM/dd/yyyy'),toDate(Filedate,'yyyy/MM/dd'),toDate(Filedate,'dd/MM/yyyy'),toDate(Filedate,'MMddyyyy'),toDate(Filedate,'yyyyddMM'),toDate(Filedate ,'MMddyyyy'),toDate(Filedate ,'yyyyMMdd'))

Your expression returns the first valid date conversion and the first valid format parameter for the data like 20220301 is yyyyddMM.

Answer 2

And if you have a fix date format = yyyyMMdd in file name, then why do you use that many date format parameter; don't make any sense and will cause issues like you have?