Right,
if I was working with a static UI I will do that. But not the case.
No sweat. I thought the was a way but since there is not, I will just bring it as a char.
The order is not that good but at least is readable.

thanks

>SQL Server does not have a date datatype so there is no function to do the conversion. The best you can do is to convert it to a nvarchar() and then back to a datetime BUT, you'll still end up with a time component, it'll just be midnight.
>
>Sometimes this type of functionality is best left to the UI.
>
>-Mike
>
>>no,
>>a DATETIME field to a DATE.
>>In other workds I want to use a TTOD() function in SQL server.
>>
>>The reason I want to do this is because I'm creating a SQL view, so that when I get all the fields in my cursor I just slap it into the grid.
>>Otherwise, it will be easy to do it in Fox, but I have too many fields to do a SELECT on that.
>>
>>
>>
>>>Sorry, I didn't understand that you needed the result as a datetime:
>>>
>>>CONVERT(datetime, CONVERT(varchar, datetimefld, 102))
>>>
>>>Is that what you need?
>>>
>>>>Yea, still a char.
>>>>I can't believe they did not think of the need for a function in these cases.
>>>>
>>>>oh well
>>>>
>>>>thanks Chris
>>>>
>>>>
>>>>>How about this:
>>>>>
>>>>>CONVERT(varchar, datetimefld, 102)
>>>>>
>>>>>or
>>>>>
>>>>>CONVERT(varchar, datetimefld, 111)
>>>>>
>>>>>
>>>>>>Chris,
>>>>>>107 still I cannot Sort, and 112 will be hard to read for the user.
>>>>>>
>>>>>>any other ideas?
>>>>>>
>>>>>>
>>>>>>>I think this will give you the order you want:
>>>>>>>
>>>>>>>CONVERT(varchar, datetimefld, 112)
>>>>>>>
>>>>>>>While this gives the formatting:
>>>>>>>
>>>>>>>CONVERT(varchar, datetimefld, 107)
>>>>>>>
>>>>>>>
>>>>>>>>On a datetime field I need to get only the date, not the time.
>>>>>>>>
>>>>>>>>I've tried LEFT(CAST(datetimefld AS CHAR),11) but it gives me, of course, a char field that I cannot sort by correctly.
>>>>>>>>How can I get this as a DATE ?
>>>>>>>>Jan 11 2003 looks nice for what I want, but again, I cannot order it in this way