Dan,
The fact that SQL Server only has a datetime data type shouldn't be a problem when working with a result set in VFP. You can easily convert a datetime to a date using TTOC(). As you mentioned, building a selection criteria where you usually want to ignore the time part is a bit more tricky. I use the following method:
SQLEXEC(lnSql, "SELECT * FROM orders WHERE DATEDIFF(day, OrderDate, '1997.05.01') = 0")
SQLEXEC(lnSql, "SELECT * FROM orders WHERE DATEDIFF(day, ShippedDate, '1997.05.30') = 0")
SQLEXEC(lnSql, "SELECT * FROM orders WHERE DATEDIFF(day, '1997.05.30', ShippedDate) > 0")
SQLEXEC(lnSql, "SELECT * FROM orders WHERE DATEDIFF(day, GETDATE()-10, ShippedDate) > 0")
SQLEXEC(lnSql, "SELECT * FROM orders WHERE DATEDIFF(year, ShippedDate, GETDATE()) = 3")
HTH
>I'm new to VFP/SQL6.5 and wanted to be sure I've got this right. I've seen several messages about using CAST/CONVERT to extract the date from an SQL DateTime field using SQLExec(), but find it hard to believe it's not easier.
>
>I have a table with DateTime fields. I would like to use both the Date and Time portions, sometimes independantly, sometimes together for calculating differences. I want to create a SELECT statement that returns all records in the table for a given date(regardless of time). Is the CAST/CONVERT method the easiest way to do this?
>
>Since I'm just getting started, would it be easier to store the DATE and TIME in the SQL table as characters and use ctot() and ctod() to work with them in VFP once I have them in a cursor? If so, what reason is there to use the DateTime field in SQL?
Daniel