CSV File Loads Dates as Null values Azure Data Factory

Question

While loading CSV files with dates formatted like 6/14/2007, if the Azure SQL table field allows Null values, then the date fields load, but as Null and not the date value. If the field is Not Null regardless of datatype I get an error that the field does not allow null values. I've Googled and tried adding a derived column and putting several different versions of the following code: toString(toTimestamp(<Your_Column_Name>,'MM/dd/yyyy HH:mm:ss'),'yyyy-MM-dd HH:mm:SS') but always return null values. Also after Googling I've tried changing the format in the Source Projection tab to yyyy-MM-dd and I still get the error that I can't load Null values although there are no Null values. It appears that I'm not the only one who has had this problem. Please advise.
I'm using Azure Data Factory

Answer 1

Hello @Kim Avery (admin) ,

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

Use the derived column transformation to generate new columns in your data flow or to modify existing fields.

In ADF, you can use the toDate expression language to convert input data string to date.

toDate expression: Converts input date string to date using an optional input date format. Refer to Java's SimpleDateFormat for available formats. If the input date format is omitted, default format is yyyy-[M]M-[d]d. Accepted formats are :[ yyyy, yyyy-[M]M, yyyy-[M]M-[d]d, yyyy-[M]M-[d]dT* ]

Examples:

1. toDate('2012-8-18') -> toDate('2012-08-18')
2. toDate('12/18/2012', 'MM/dd/yyyy') -> toDate('2012-12-18')

For more details, refer Derived column transformation in mapping data flow.

Hope this helps. Do let us know if you any further queries.

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

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.