Results 1 to 7 of 7

Thread: Loading datetime data using bcp/bulk insert

  1. #1
    Bob Guest

    Loading datetime data using bcp/bulk insert

    Short version:
    The best/fastest way to load large amounts of data from a comma delimited text file into an SQL Server table. Where the text file contains date fields in ccyy/mm/dd format and the SQL Server table defines those fields as datetime data types.

    Details:
    When I attempt to load files (using either bcp or BULK INSERT) containing datetime data the load process errors because the datetime fields in my text file are in ccyy/mm/dd format and the default format for SQL Server is mm/dd/yy. I have been unable to change the default format by using the SET DATEFORMAT statement (apparently the SET DATEFORMAT statement will not work for bcp because bcp runs outside of the SQL Server session???).
    The only alternatives that I have come up with are: 1) Change the format of date fields in the text file from ccyy/mm/dd to mm/dd/ccyy. 2) Create a temporary table that defines the date fields as a char(n) datatype. Then load the data into the temp table. Then SET the DATEFORMAT to ccyy/mm/dd. Then copy the temp table into the permanent table (the permanent table using datetime data types).

    Both of these alternatives would require additional processing time. Since this is a process that loads large amounts of data on a monthly (soon to be weekly) basis, speed is of the essence.

    I would appreciate any suggestions.

    Thanks!

  2. #2
    Carles Guest

    Loading datetime data using bcp/bulk insert (reply)

    On 12/11/98 7:31:42 AM, Bob wrote:
    > Short version:
    The best/fastest way to load large amounts of data from a
    > comma delimited text file into an SQL Server table. Where the text file
    > contains date fields in ccyy/mm/dd format and the SQL Server table defines
    > those fields as datetime data types.

    Details:
    When I attempt to load
    > files (using either bcp or BULK INSERT) containing datetime data the load
    > process errors because the datetime fields in my text file are in
    > ccyy/mm/dd format and the default format for SQL Server is mm/dd/yy. I
    > have been unable to change the default format by using the SET DATEFORMAT
    > statement (apparently the SET DATEFORMAT statement will not work for bcp
    > because bcp runs outside of the SQL Server session???).
    The only
    > alternatives that I have come up with are: 1) Change the format of date
    > fields in the text file from ccyy/mm/dd to mm/dd/ccyy. 2) Create a
    > temporary table that defines the date fields as a char(n) datatype. Then
    > load the data into the temp table. Then SET the DATEFORMAT to ccyy/mm/dd.
    > Then copy the temp table into the permanent table (the permanent table
    > using datetime data types).

    Both of these alternatives would require
    > additional processing time. Since this is a process that loads large
    > amounts of data on a monthly (soon to be weekly) basis, speed is of the
    > essence.

    I would appreciate any suggestions.

    Thanks!


    I thinck the second option is the best.
    I execute bcp every night (aprox. 150.000 records), with datatime fields,
    and I use a temporay table with NO indexes,
    the bcp program with de parameter /bXXXXX run fine.


  3. #3
    Join Date
    Jul 2007
    Posts
    3

    Question datetime insert issue with bcp_bind API

    I am using bcp_bind API for bidning with the column of type 'datetime'. The time stamp variable in program is of type char (i am using C++). So the bcp_bind call is failing and in turn bcp_batch also is failing.

    Does any one has faced this issue? How can i fix this issue?

    Thanks.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Posssible to cast source timestamp data in datetime style?

  5. #5
    Join Date
    Jul 2007
    Posts
    3
    i can do the casting, but still bind fails

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Do you have error message? Did you post the issue in VB forum?

  7. #7
    Join Date
    Jul 2007
    Posts
    3
    Thanks for help.
    Actually i got solution for this, we need to get hold of DBDATETIME structure for binding.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •