3

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Beginner_SSIS
  • 31
  • 1
  • 2
  • 1
    Dates have no format. They are binary values. If you use a date type to store dates, eg `date`, `datetime` etc, the stored value won't have any format. Formats apply only when strings are parsed into dates or dates are formatted as strings for display – Panagiotis Kanavos Jun 12 '19 at 10:41
  • Are you sure you aren't confusing the format used by your *client* tool (SSMS is just a client tool) for some actual date format? SSMS uses the ISO8601 format to avoid confusion. Other client tools (eg desktop applications) will use the user's locale to format the dates – Panagiotis Kanavos Jun 12 '19 at 10:43
  • 2
    What is `12/06/2019` supposed to mean anyway? June 12th (today) or December 6th? If it's June 12th, the CSV dates are in DD/MM, not MM/DD. The culture used to parse dates and numbers in a flat file is controlled by the `LocaleID` setting in the Flat File source. If you want `MM/DD`, change the locale to `en-US` – Panagiotis Kanavos Jun 12 '19 at 10:47
  • 1
    Possible duplicate of [How to import a flat file without changing regional settings on the deployment server?](https://stackoverflow.com/questions/14647532/how-to-import-a-flat-file-without-changing-regional-settings-on-the-deployment-s) – Panagiotis Kanavos Jun 12 '19 at 10:49
  • Are you *sure* the data refers to December 6th? It's far, far more likely that the file contains *today's* records, (June 12th) and there was no reversal. `YYYY-MM-DD` is the ISO8601 format for displaying dates. The order of its components doesn't change – Panagiotis Kanavos Jun 12 '19 at 10:55
  • Have you solved the issue? You didn't accepted or replied to one of the existing answers? – Yahfoufi Jul 05 '19 at 09:37

2 Answers2

0

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

SSharma
  • 951
  • 6
  • 15
0

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124