>Hi,
>
>Still struggling through a VFP to SQL Server 2012 conversion. I have been using the Sedna Upsizing Wizard. VFP uses a DATE format of mmddyyyy and when I use the default 'datetime' it rejects invalid dates but brings over most of the records. Although it is stored on SQL as a datetime data type, it seems to display OK on my app. Has anyone had any experience as to which type should be used on a VFP app, datetime, datetime2 or date?
>
>TIA,
>
>Bill

I would recommend the 32 bit ODBC SQL Server Native Client 10 (not 11). The Native 10 driver supports the new Date and DateTime2 as well as the old standby of DateTime SQL Server Datatypes. Other drivers will bring down DateTime2 and maybe even Date as character to VFP. They kinda look like dates, but don't be fooled. Check the value with Type() or VarType() and they will be Character type. I would suggest that you absolutely avoid using a SQL Character or Varchar to store dates. Never a good idea.

DateTime2 in SQL Server, IMO, is the preferred DateTime datatype and works fine with VFP. I always use DateTime2 when possible. And as pointed out the old SQL DateTime stops at 1753 which is the start of the Gregorian calendar.

I don't believe that any ODBC driver works with SQL Server Varchar(MAX) and FoxPro. I believe those MAX values come down to VFP as blanks. So, hopefully you have control of the schema in SQL Server or you will have a problem with MAX. However, and I don't have the ability to test this, but maybe Native Client 11 works with MAX, but then you give up DateTime2.

As far as the SQL Server SmallDateTime datatype goes I believe that only the plain old ODBC SQL Server (not native) works with that. I would avoid SmallDateTime as I don't think it save you that much

You should test the various drivers and the various SQL datatypes to verify what I've said here because I am saying this from memory.

But for me, bottom line is that I prefer the ODBC SQL Server Native 10 driver.