Importing a CSV date column of yyyy-mm-dd hh:MM:ss:xxx RRS feed

  • Question

  • I know I've posted this to the wrong forum, but the dropdown for Forum category/ Forum doesn't show SQL Server or anything that appears close to it to me.  My guess is that this forum isn't correct either, but if someone could direct me to where I can get the correct help I'd be grateful.  

    I have some large CSV files that I'm trying to import into a SQL Server table using ssms import wizard.  One of the columns is a date column formatted as yyyy-mm-dd hh:MM:ss:xxx.  I've tried every date format in the source field that is offered to me, but it, at best, gives me the entire date with the milliseconds as .000 rather than the value that is in the .txt CSV file. 

    The only thing I can think of is that the input date has the milliseconds after a colon (:) rather that a period (.).  I could write a program that converts the colon to a period and then load it, but I did edit the file and changed the first six rows to period and that didn't seem to have any effect.  

     For the import I'm using "Flat File Source", under "Advanced", I select "DataType" for the column and have used every one of the following as the DataType.

    database date [DT_DBDATE],  database time [DT_DBTIME], database time with precision [DT_DBTIME2],

    database timestamp [DT_DBTIMESTAMP], database timestamp with precision [DT_DBTIMESTAMP2],

    BTIMESTAMPOFFSET] and even date [DT_DATE], decimal [DT_DECIMAL], file timestamp [DT_FILETIME]

    The Destination is to SQL Server Native Client 11.0 to destination Type of datetime2 which is defined on the table as datetime2(7), not null.  

    With all these attempts I either get a "data would be truncated" error, or it would load with the milliseconds set to 000 rather than the value that is in the CSV file.  

    I would appreciate any help. 



    Tuesday, November 24, 2020 4:56 AM

All replies