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!

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.

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.

Posssible to cast source timestamp data in datetime style?

i can do the casting, but still bind fails

Do you have error message? Did you post the issue in VB forum?

Thanks for help.
Actually i got solution for this, we need to get hold of DBDATETIME structure for binding.