Post reply

Import 2010-09-07T10:40:03 with BCP Utility?

  • September 7, 2010 at 2:04 pm

    #226658

    Can you import the value 2010-09-07T10:40:03 into a DateTime field with the BCP Utility?

    The "T" seems to be a problem...

    NOT options: importing into a varchar field or using bulk insert

    This question is very specific, Can you:

    - import the value 2010-09-07T10:40:03

    - into a DateTime field

    - with the BCP Utility?

    Thanks in advance for the help!

  • September 7, 2010 at 2:16 pm

    #1217593

    Did you try using bulk insert instead of bcp? Seems to work...

    I created a file test.txt holding 2010-09-07 10:40:03.000.

    The following code did work without any issues:

    DECLARE @tbl TABLE

    (

    col1 DATETIME

    )

    INSERT INTO @tbl

    SELECT *

    FROM OPENROWSET(BULK N'C:\TempSSC\test.txt', SINGLE_CLOB) AS col

    SELECT *

    FROM @tbl


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 7, 2010 at 2:26 pm

    #1217598

    Hi Lutz

    Thanks for taking the time to reply. Yes, Bulk insert works for us also.

    The question is specifically about the BCP Utility:

    Can you import values that contain the "T"?

    Thanks

    George

  • September 7, 2010 at 3:32 pm

    #1217646

    I tried using different kinds of format files but I heven't been able to import directly into the target table.

    When I'm facing such circumstances and can't find a workaround I usually go with a staging table: import the data with the bcp utility into a table with a varchar() column for the datetime values and copy those values to the final target table in a separate step.

    As an additional benefit you could do some data validation before the final insert.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply