INF: Using BCP to Import NULL Values into Datetime Columns

ID: Q66681

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

When the bulk copy program (BCP) imports a fixed-length character field that contains spaces into a datetime column that allows nulls, it is converted to the default date of "Jan 1, 1900". The following are three methods to import the date as a NULL value:

If the field is in the last column of the input file and it contains no spaces or characters, BCP will detect the row terminator and place a NULL value into the datetime column of the table. This method works only for the last column of the table, thus limiting you to one datetime column per import file.

An alternate method of importing NULL dates into a table involves creating a variable-length import file with field terminators. If there are no characters between two field terminators, BCP will import a NULL value.

If the creation of a variable-length import file is not feasible, you can use BCP to import the fixed-length character file and allow the columns that contain blanks to be inserted with the default date. Once the table is loaded, a simple update could be used to change all fields in the column that contain "Jan 1, 1900" to NULL.


Keywords          : kbtool SSrvBCP 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 10, 1999