CSV File Loads Dates as Null values Azure Data Factory

Kim Avery (admin) 1 Reputation point
2021-05-19T15:01:19.553+00:00

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

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

1 answer

Sort by: Most helpful
  1. PRADEEPCHEEKATLA-MSFT 73,646 Reputation points Microsoft Employee
    2021-05-21T07:26:03.547+00:00

    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')

    98449-image.png

    95169-image.png

    98525-adf-string2date.gif

    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.