INF: How to BCP in Datetime Values in Nondefault Format

Last reviewed: April 3, 1997
Article ID: Q89080

The information in this article applies to:
  • 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.


Additional query words: 4.20 dblib Windows NT
Keywords : kbtool
Version : 4.2
Platform : OS/2 WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.