Dataflow String to Date Conversion

Question

I have a dataflow with a derivate column activity, in this activity I create a new column based on another column using this expression:

iif(isDate(toString(i1), 'yyyyMMdd'),concat(substring(toString(i1), 1, 4),'/' ,substring(toString(i1), 5, 2),'/',substring(toString(i1), 7, 2)),'')

The goal is to format the string as yyyy/MM/dd if the input is a valid date otherwise return empty.

This works as expected but when using the output of this activity in a sink, I map this new column to a date type column in the sink, I get the warning that some columns have different data types, when doing data preview on the sink, the Date column is empty even that the derivate step has data for the column.

It seems that the sink cannot cast the string value to a date so it sets the column to NULL, what are my options? Any way to force the derivate column to be of type Date? I don't see any expression that would allow me to format a date and return it as a date.

Answer 1

Try to use a derived column transformation instead :

iif(
    isDate(toString(i1), 'yyyyMMdd'),
    toDate(concat(substring(toString(i1), 1, 4), '/', substring(toString(i1), 5, 2), '/', substring(toString(i1), 7, 2)), 'yyyy/MM/dd'),
    null()
)