BCP date format

Ted Dykhorst 1 Reputation point
2020-08-21T03:31:31.837+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,758 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2020-08-21T03:54:29.907+00:00

    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

    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2020-08-21T05:50:13.517+00:00

    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

  3. Tom Phillips 17,716 Reputation points
    2020-08-21T12:20:35.923+00:00

    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.

    0 comments