How to convert a date field to short date? without time.

I am using a query builder to buld a sql statement which brings me somes fields which are defined as date, and they are brought like this one: 5/17/2006 12:00:00 AM and I just want to bring the 5/17/2006.

I tried

CONVERT (CHAR(10), Fecha_evento, 101) ASFecha_evento

It works, for displaying, but when I try to look for the maximun within that field, it interprets it as a string, so I do not get the right value. Do you have a way to solve this? thanks a lot!

There's really no such thing as a date without a time. If you're having problems with displaying the information on a form, you should look at formatting the value, rather than changing the actual value that's retrieved. Setting your format string to "{0:MM/dd/yyyy}" should format it as you've indicated above.