SSIS - Month and date position got interchanged after using Data conversion

Problem:

I am trying to load some data from csv file to my SQL server. There is a column for Date which has data type Unicode (WSTR) data type in csv file and the column for storing date in SQL server is of Datetime data type.

When I used DATA CONVERSION transformation to convert WSTR data to DBTIMESTAMP data, it got changed but with an error that it interchanged the month and date which gives me the wrong date.

Like Date should be like 2019-09-03 (For 3rd Sep 2019), but it gives me 2019-03-09.

Please suggest what's the issue i am facing?

Solution:

Problem cause

This may occurs when converting a string to a date value without specifying the date format. Reffering to the SSIS data conversion transformation official documentation:

If you are converting data to a date or a datetime data type, the date in the output column is in the ISO format, although the locale preference may specify a different format.

Assume that the data is stored in the csv file with the following date format MM/dd/yyyy and the default date format in the regional settings is dd/MM/yyyy then date values will not be converted properly.

Solution

In order to specify the date format you have to use a Script Component or a Derived column.

Derived Column

You have to reorder the date part manually, you can use TOKEN() and TOKENCOUNT() function to do that, the following expression convert dd/MM/yyyy format into yyyy-MM-dd universal format:

TOKEN([DateColumn],"/",3) + "-" + RIGHT("0" + TOKEN([DateColumn],"/",2),2) + "-" +RIGHT("0" + TOKEN([DateColumn],"/",1),2) 

Script Component

You can use DateTime.ParseExact() function to parse a date based on a specific format:

DateTime.ParseExact(Row.DateColumn,"MM/dd/yyyy",System.Globalization.CultureInfo.InvariantCulture");

Original Post: https://stackoverflow.com/questions/57843330/month-and-date-position-got-interchanged-after-using-data-conversion/57845393#57845393