Bad conversion date nvarchar to datetime in dataflow on SSDT package (.dtsx)

Question

I have a strange problem with a automatic conversion date in SSIS package. I develop with Visual Studio Shell 2010.

In input, i have *.csv with different date column and more.

First step, I import row data in CasiopFLUX base (SQL Server 2012).

All column type are nvarchar(2500). Format date are : MM/DD/YYYY

Second step, i use OLEDB source Destiation for import data in CasiopDATA base :

On exucution, you can see result :

Data type in this database are Datetime :

My problem is the follows :

• format datetime on Modifiedon colomn is OK
• format datetime on InitialDeliveryDate is KO : inversion of DD MM
• format datetime on PlannedDeliveryDate is KO : inversion of DD MM

I don't understand where the problem comes from. In my SSIS task properties, LocaleID is configured in "English (UK)". All software are installed in English, Regional setting on my server are too in English.

I'm a little confused, do you have any idea?

thank you very much in advance

Answer 1

English uses dd/MM/yyyy, Americans use mm/dd/yyyy. If you have a date like 02/03/2019 and your locale is England, then it'll be inferred as 02 March 2019 not 03 February 2019 (which it would be if your locale is America).

Personally, when using ambiguous date formats with SQL Server, and the data in the excel sheet isn't configured correctly as a date, I prefer to import the value as a varchar into a staging table, and then transform and insert the values into the production table.

For your data, that would mean you could use CONVERT to translate the data to the correct date yusing the Style Code 101:

CONVERT(date, InitialDeliveryDate,101)