INF: How to BCP in Datetime Values in Nondefault Format

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

Microsoft SQL Server users often need to import ASCII files into SQL Server that contain datetime values in formats other than the default formats. Since the Bulk Copy Program (BCP) recognizes only the default date format, which is either "mm-dd-yy[yy]" or "mmm dd yyyy", you must do the following to import such files:

1. Create an intermediate table that is very similar to the target table except that those columns that correspond to the datetime columns in the target table are defined as char(n) instead.

2. BCP the data file into the intermediate table.

3. In SAF, ISQL, or SQL Administrator, use the SET DATEFORMAT command to set the order of the date format according to the format used in the data file.

4. Use an INSERT statement to insert all the rows from the intermediate table to the target table. SQL Server will automatically convert the char(n) values into the appropriate datetime format.

   NOTE: It may take a long time to run the INSERT statement if the data file is very large. For a long-term solution, consider writing a custom DB-Library (DB-Lib) application using BCP libraries.

MORE INFORMATION

In SQL Server version 4.2, although you can change the date format by using the SET DATEFORMAT command, that change will be effective only for the duration of the current session. Since BCP is a separate session by itself, there is no way to change the date format for the BCP session. Therefore, you can not use the SET DATEFORMAT command to force BCP to accept a date format other than the default, which is in the "mdy" order in U.S. English.