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

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. 

Thanks,

Bob

 

Answer 1

I know I've posted this to the wrong forum, but the dropdown for Forum category/ Forum doesn't show SQL Server

You can find the SQL Server relate forums at Question&Answer: https://docs.microsoft.com/en-us/answers/products/sql-server

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Thank you for the help.

Answer 3

This forum handles requests related to Open Specifications documentation issues.  The Open Specifications can be found at: http://msdn.microsoft.com/en-us/library/cc203350.aspx.  Your question does not appear to be related to the Open Specifications documentation set. 

I suggest you try the submitting your question to Microsoft Q&A with an appropriate tag to get assistance. 

https://docs.microsoft.com/en-us/answers/products/sql-server

Mike Bowen

Escalation Engineer Microsoft Open Specifications