How to quickly select DISTINCT dates from a Date/Time field, SQL Server

Question

I am wondering if there is a good-performing query to select distinct dates (ignoring times) from a table with a datetime field in SQL Server.

My problem isn't getting the server to actually do this (I've seen this question already, and we had something similar already in place using DISTINCT). The problem is whether there is any trick to get it done more quickly. With the data we are using, our current query is returning ~80 distinct days for which there are ~40,000 rows of data (after filtering on another indexed column), there is an index on the date column, and the query always manages to take 5+ seconds. Which is too slow.

Changing the database structure might be an option, but a less desirable one.

Answer 1

This works for me:

SELECT distinct(CONVERT(varchar(10), {your date column}, 111)) 
FROM {your table name}