Microsoft KB Archive/66681

{|
 * width="100%"|

INF: Using BCP to Import NULL Values into Datetime Columns

 * }

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 &quot;Jan 1, 1900&quot;. 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 &quot;Jan 1, 1900&quot; to NULL.

Keywords : kbtool

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbZNotKeyword3 kbSQLServ420OS2