>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"
>cSQL = "select id, name, IIF(EMPTY(date1), {}, date1) AS date1 from mytable WHERE id = myid' ORDER BY id"
>oRS = OConn.Execute (cSQL)
>
>oRS.Close
>OConn.Close
>
>
>Thanks,
>
>Jerry
You need to convert your dates to NULL before sending the table to SQL Server.
The code you're trying to execute will be executed by SQL Server and therefore the syntax must be SQL Server.
Although I see now that you use VFP and not SQL for your command. If so, try EVL(DateField, NULL) as Date1
If it's not broken, fix it until it is.
My Blog