Copying Datetime or Smalldatetime Data

The bulk copy program (bcp) drops both seconds and milliseconds when bulk copying datetime or smalldatetime data out of SQL Server in character mode. The date is converted in the default date format:

MMM DD YYY HH:MM[AM/PM]

The bcp utility 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.

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.
• Create a view, use the CONVERT() function on the date datatype to save it as a character string, then bulk copy out from the view.