BCP date format

Ted Dykhorst 1 Reputation point

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.
9,119 questions
{count} votes

3 answers

Sort by: Oldest
  1. Ronen Ariely 14,141 Reputation points MVP

    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'

    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

  2. MelissaMa-MSFT 24,131 Reputation points

    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

  3. Tom Phillips 17,611 Reputation points

    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.