SSIS: Date format Issue csv file to a SQL Server table: DD and MM swapped

user_11635621 0
Viewed 3461

I have a csv file with a Date column with MM/DD/YYYY format. When I load the csv file to a SQL Server table with SSIS, I would like to have the following format: YYYY-MM-DD.

The issue is that when the csv file contain a date with a day value between 1 and 12, the day and the month are swapped.

Example:

12/06/2019 in csv file -> 2019-06-12 in SQL Server table: DD and MM are swapped
MM/DD/YYYY  ->  YYYY-MM-DD

How could I do to have 2019-12-06 in SQL Server table?

Thanks in advance for yours answers.

etl ssis csv datetime sql-server
2 Answers

I don't see why that shouldn't work.

I took an example:

1. Created a table

CREATE TABLE [dbo].[DateTable](
[TestDat] [datetime] NULL,
[TestDat1] [datetime] NULL,
[TestDat2] [datetime] NULL,
[TestDat3] [datetime] NULL) ON [PRIMARY]


2. Created a CSV file with following dates:
12/06/2019,01/06/2019,02/06/2019,03/06/2019

  1. Created an SSIS package with only dataflow. The dataflow task contains a Flat File Source and an OLE DB Destination.
    The DataFlow task looks like this:
    enter image description here
    And the mapping in OLE DB Destination looks like this:
    enter image description here

After running the SSIS package, I get the following data populated to SQL Server Table.
It is has always taken 06 as Date.
SQL Table Result

user_116131180

This is a common issue, since when handling MM/dd/yyyy and dd/MM/yyyy date string formats and month and day are less than 12.

Since csv files are text files date are stored as text, implicitly converting these fileds to a datetime filed (sql database) may cause this issue.

Best way to insure that dates are converted correctly is to add a Script component, add a Output column of type DT_DATE (exampl: outDate) and use DateTime.ParseExact() function to force converting using MM/dd/yyyy format:

Row.outDate = DateTime.ParseExact(Row.csvDateFiled,"MM/dd/yyy",System.Globalization.CultureInfo.InvariantCulture);

You can refer to the following similar question for more details:

user_70312300
Related