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.
8,561 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Ronen Ariely 13,786 Reputation points Microsoft MVP
    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

    No comments

  2. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    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,511 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.

    No comments