INF: Using BCP to Import NULL Values into Datetime Columns

ID Number: Q66681

1.10 1.11 4.20

OS/2

Summary:

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:

1. 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.

2. 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.

3. 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.