BCP date format

Question

I am attempting to import data using bcp. The data contains dates formated as Jan 03 2019 12:00:00:00.000000AM.
BCP rejects this data, saying this is an invalid date format.
I believe BCP expects a date formatted as 2019-01-03 12:00:00:00.000000.

Please advise how to change the expected data format.

Many thanks

Answer 1

Good day,

Can you explain what is this time: 12:00:00:00.000000AM

12             > hours
00             > minutes
00             > seconds
00.000000 > What is this part?!?

Your time has 4 parts instead of three. Are you sure that the time is not something like: 12:00:00.000000AM ?

If the time is like I posted above then you can use CONVERT to DATETIME2 using style 9

declare @txt NVARCHAR(100)
SET @txt = 'Jan 03 2019 12:00:00.000000AM'
SELECT CONVERT (DATETIME2, @txt, 9)

In this case, you can import the data into a staging table and then migrate the data to the original table using CONVERT as explained above

Another option is to use select from OPENROWSET and use CONVERT on the fly

Answer 2

HI Ted,

BCP uses the ODBC bulk copy API. Therefore, to import date values into SQL Server, BCP uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]).

Please refer more details in Copying Date Values.

Your data should be 'Jan 03 2019 12:00:00.000000AM' instead of 'Jan 03 2019 12:00:00:00.000000AM'.

In order to change the expected data format, we recommend you to use TRY_CONVERT . The difference between CONVERT() and TRY_CONVERT() is in the way they handle data types that can’t be converted. One throws an error, while the other returns null.

SET DATEFORMAT mdy;    
SELECT TRY_CONVERT(datetime2, 'Jan 03 2019 12:00:00.000000AM') AS Result;   
  
/*  
2019-01-03 00:00:00.0000000  
*/  

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 3

You will be much happier if you BCP your data into "stage" tables with all varchar fields, and do not attempt to use data types. Then merge the data from the "stage" table to the target table using TSQL.

Yes, it will take more time, but you can handle data conversions and data errors.