Thanks but, this caused the whole row with a blank date1 to be null, but the non blank date1 rows were ok.

Any other suggestions?

Jerry



>Try
>

>select id, name, IIF(EMPTY(date1), CAST(null AS D), date1) AS date1 from mytable WHERE id = myid ORDER BY id
>

>
>>We have a foxpro dbf file that contains a date field 'date1'. We need to import this dbf to SQL Server using the OLEDB driver. Sometimes the date1 field contains a blank date. We need to convert blank date data into a null. But when we run the following code, any blank date comes back as the date '12/30/1899' instead of a NULL.
>>
>>How can we convert blank dates into nulls using the following?
>>
>>

>>OConn = CREATEOBJECT("ADODB.Connection")
>>OConn.ConnectionString = "provider=vfpoledb.1;;
>>	data source=c:\\mydata\;Exclusive=false;Nulls=false"
>>OConn.Open
>>
>>cSQL = "select id, name, IIF(EMPTY(date1),  null, date1) AS date1 from mytable WHERE id = myid' ORDER BY id"  &&does not work
>>cSQL = "select id, name, IIF(EMPTY(date1),  {}, date1) AS date1 from mytable WHERE id = myid' ORDER BY id"  &&Date1 = 12/30/1899
>>oRS = OConn.Execute (cSQL)
>>
>>oRS.Close
>>OConn.Close
>>

>>
>>Thanks,
>>
>>Jerry