>Hi Cetin,
>
>It's been sometime.... how have you been ?
>
>Problem here is the datefield does have some data in it for some records and not for others. I was looking into formatting options with on the SQL side but there's nothing obvious to use. There's NULLIF function but I'm not exactly sure how to use it. How do I send empty date fields as a NULL ?
>{//} ? I assume you mean setting any blank date fields on the foxpro side to null first then set SQL to allow nulls for that column then initiate the transfer. If so can this be done from foxpro for windows tables.
>
>thanks,
>
>Don

Hi DOn,
Thanks I'm fine.
There are formatting options on the SQL side too. If you already transferred the data you might want to do something like a replace :
-Assuming you're replacing a field named myDate and it's set to accept nulls (all 1899/12/30 representing an empty date) :

* Using query analyzer
update myData set myDate = NULL where convert(char(10), myDate, 102) = '1900.01.01' 

* VFP SPT
ldDate = {^1900/1/1}
SQLExec(lnHandle, "update myData"+;
  " set myDate = NULL where myDate = ?ldDate")

Note that the value inserted for empty dates is either 1900/1/1 or 1899/12/30. Check first.

If you haven't transferred yet you could do all on VFP side (thinking this is a one time job and data is not huge I don't care about efficiency) :

Code assumes you have a table named myData with 2 field myDate d and myValue c(30)

lcFox2x = 'c:\mypath\myData.dbf'
Select * From (lcFox2x) Into Cursor myCopy Readwrite
For ix=1 To Fcount()
  If Type(Field(ix))='D'
    lcColumn = Field(ix)
    Alter Table myCopy Alter Column &lcColumn d Null
    Update myCopy Set &lcColumn = .Null. Where Empty(&lcColumn)
  Endif
Endfor

Local lnHandle, lcDatabaseName
lcDatabaseName = "myVFPUpsize"
lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
    'SERVER=servername;Trusted_connection=Yes')
If SQLExec(lnHandle, "create database "+lcDatabaseName) < 0
  Do errHand
  Return
Endif
mySQLExec(lnHandle, "use "+lcDatabaseName)
mySQLExec(lnHandle, ;
  "create table myData"+;
  " (myDate datetime null, myValue varchar(30))")

Scan
  mySQLExec(lnHandle, "insert into myData (myDate,myValue) values"+;
     " (?myCopy.myDate, ?myCopy.myValue)")
Endscan
mySQLExec(lnHandle, "select * from myData","crsResult")
SQLDisconnect(lnHandle)
select crsResult
browse

Function mySQLExec
  Lparameters tnHandle, tcSQL, tcCursorName
  tcCursorName = Iif(Empty(tcCursorName),'',tcCursorName)
  If SQLExec(tnHandle,tcSQL,tcCursorName) < 0
    Do errHand With tcSQL
  Endif

Function errHand
  Lparameters tcSQL
  lcError=tcSQL+Chr(13)
  Aerror(arrCheck)
  For ix=1 To 7
    lcError = lcError+Trans( arrCheck [ix])+ Chr(13)
  Endfor
  Messagebox(lcError,0,'Error def.')

Using afields() you could make this code to work with any table, writing the mapping for you etc. :) IMHO sufficient for a one time work.
Cetin