PRB: Character Mode BCP Drops Seconds and Milliseconds

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 
Issue type        :