PRB: Character Mode BCP Drops Seconds and Milliseconds

 Last reviewed: April 28, 1997
Article ID: Q100077

The information in this article applies to: 

  - Microsoft SQL Server Programmer's Toolkit, version 4.2

SYMPTOMS

Version 4.2 of the bulk copy program (BCP) drops both seconds and milliseconds when bulk copying DATETIME or SMALLDATETIME data out of SQL Server in character mode.

CAUSE

When DATETIME or SMALLDATETIME information is extracted from SQL Server, the date is converted in the default date format: 

   MMM DD YYY HH:MM[AM/PM]
BCP uses the default date format. When BCP is executed in character mode, both DATETIME and SMALLDATETIME data are converted to the default date format. Thus, seconds and milliseconds are not displayed.

WORKAROUND

If it is critical to BCP the date datatype information out with both the seconds and milliseconds, one of the following methods can be used:

  • Native mode BCP can be used if the data is to be transferred between various SQL Servers where the tables are identical in structure.
  • Version 1.1 BCP can be used in character mode if dealing with DATETIME data (SMALLDATETIME was not implemented in version 1.1). BCP version 1.1 is based on DB-Library 1.0, the default date format at that time included seconds and milliseconds. The default was changed starting with version 1.1
  • Use SELECT INTO to copy the data to an intermediate table using the CONVERT() function on the date datatype to save it as a character string. Then BCP out from the intermediate table.

Additional query words: data type dblib
Keywords : kbtool SSrvBCP
Version : 4.2 | 4.2 | 4.2 4.2a
Platform : MS-DOS 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 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.