sql-servercsvdateimportwizard

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:

02-04-2021
03-04-2021
05-04-2021
06-04-2021
07-04-2021
08-04-2021
09-04-2021
10-04-2021
12-04-2021
13-04-2021
14-04-2021
15-04-2021
16-04-2021
17-04-2021
19-04-2021
20-04-2021
21-04-2021
22-04-2021
23-04-2021
24-04-2021
26-04-2021
27-04-2021
28-04-2021
29-04-2021
30-04-2021

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:

2021-01-04
2021-02-04
2021-03-04
2021-05-04
2021-06-04
2021-07-04
2021-08-04
2021-09-04
2021-10-04
2021-12-04
2021-04-13
2021-04-14
2021-04-15
2021-04-16
2021-04-17
2021-04-19
2021-04-20
2021-04-21
2021-04-22
2021-04-23
2021-04-24
2021-04-26
2021-04-27
2021-04-28
2021-04-29
2021-04-30

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;