[SOLVED] Date import from CSV to Microsoft SQL Server

Date import from CSV to Microsoft SQL Server

I'm trying to import csv file containing data to my SQL server using SQL server import wizard in Microsoft SQL server management studio.

CSV file date looks like this:

In the wizard I chose source DataType as date, and destination table has datatype date (i tried also datetime, datetime2, all the same trouble) Imported completed successfully, but the result is:

As you can see, days and months mixed, date is wrong. Some rows are correct, but in some rows there is a month instead of day.

What can I do?

Solution

Here is how to do via T-SQL in SSMS.

As @Lamu pointed out, input file date format is ambiguous. So the following line specifies it exactly:

SET DATEFORMAT DMY;

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.tbl;

CREATE TABLE dbo.tbl 
(
   inputDate      DATE
);

-- to let know format of the date in the *.csv file
SET DATEFORMAT DMY;

BULK INSERT dbo.tbl 
FROM 'e:\temp\Faenno.csv'
WITH  
(
   DATAFILETYPE    = 'char', --widechar',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n',
   CODEPAGE = '65001'
);

SELECT * FROM dbo.tbl;