INFO: BCPing in Just the Date of Datetime Column (67659)


The information in this article applies to:

  • Microsoft SQL Server 4.2x
  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q67659

SUMMARY

This article discusses what happens when just the date is BCPed into a datetime field.

MORE INFORMATION

It is possible to BCP just the date into a datetime field. The storage size will be the same as if you had both the date and time. For a datetime column, this is 4 bytes for the number of days before/after January 1, 1900, and 4 bytes for the number of milliseconds after midnight on that day. If you select what you have input with BCP from SQL Server, you will see the time "12:00AM" on the end of each date because the number of milliseconds after midnight is 0 (zero).

To display just the date portion of a datetime field, use the style parameter of the convert() function. For example: 
   select
   convert(char(12),getdate(),3)
Modification Type:MinorLast Reviewed:2/14/2005
Keywords:kbinfo KB67659
©2004 Microsoft Corporation. All rights reserved.