>>Is there a way via SQL-pass-thru to return a table from SQL Server with datetime fields as dates?
>>
>>Would creating a datatype on SQL Server to limit the portion of the datetime field to just display the date be an option?
>>
>
>Are you doing a SQL query in VFP? If you're querying the SQL Server and populating a VFP cursor, the datetype for the datetime field will be DATETIME and you could then use:
>
>DatePortion = DTOC(DateTimeFieldName_InCursor)
>
>to do what you like with the DatePortion piece but note that this is then of CHAR type. If you need it as DATE type, then just use:
>
>DatePortion = CTOD(DTOC(DateTimeFieldName))
>
>BUT, the real question is, why do you want to do this at all? If you store a DATETIME value in a DATE type field in a table, you get just the date portion of the DATETIME value. Further, all commands that operate on a DATE type will work exactly the same on a DATETIME type.
>
>However, you can - if you insist - use an 'inline' conversion function in MS-SQL using the DATEPART function. SQL has no built-in function for returning the date portion of a DATETIME field per se because it's DATE type is actually DATETIME. But you can build one - sortof:
>
>=sqlexec(SQL_Handle,"
> SELECT
> CONVERT(char(2),DATEPART(Mm, Date_Time_Field))
> +'/'
> +CONVERT(char(2),DATEPART(Dd, Date_Time_Field))
> +'/'
> +CONVERT(char(4),DATEPART(Yy, Date_Time_Field))
> FROM SomeTable")
>
>This will return a CHARACTER "date" but without leading zeros (i.e. 6/21/2001 not 06/21/2001). You CANNOT return just the "date" portion as DATE vartype. But, if that's what you want, see what I said above. The CONVERT function is needed to convert the numeric Yy, Mm and Dd to char types so you can concatenate the '/' - it'll error otherwise. Note also that Mm, Yy and Dd are legal SQL abbreviations for the DATEPART [whichpart] parameter. Use them just like that.
>
>So, in closing, if you're just trying to get Foxpro DATE vartypes, don't worry about it and just pull the thing in as is. When you write it out to your VFP table, or manipulate it like a date, it's all the same.
>
>Good luck,
>
>-Arne
>
>
>
Hi Arne,

One correction...

DatePortion = DTOC(DateTimeFieldName_InCursor)

I believe you meant TTOC Because SQL returns a DateTime, right?
I use TTOD() to convert where required.
HTH,