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