Advice with date column SQL Server 2005 I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example):

WITH Dates AS ( 
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] &lt; @EndDate
) 
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))


Now this results in a full Clustered Index Scan (surprise, surprise). I am trying to think of ways of making this faster (the actual query takes 3 mins :O). I have thought of doing the following but haven't tried any yet as I ran out of time earlier:


Use a computed column with just the date part
Index said computed column (Not sure if this is even possible?)
Use an Indexed view (Again, not sure if this is possible, will work?)


Easiest way would be to update the column and remove all time information, but I cannot do this :( 

Any ideas?

Update

Thanks for all of the answers so far. I think the point of the question was missed because I was slightly unclear on what I wanted. My bad. I was only aiming to optimise the date conversion part of the query as the amount of data i'm dealing with is tiny in reality ( &lt; 500,000 after the cross apply with a year long date range). Sorry for the confusion on this.

For those optimising the rest of the query for me, I can see what people are saying by using &lt; &gt; but consider the following:


The parameters that are passed in is a date range (eg. 1st to the end of this month)
The start date in the table can appear before or during the parameters date range (eg. only the end date is in the date range)
The end date in the table can appear during or after the parameters date range (eg. only the start date is in the date range)
Lastly, the start and end date in the table is in the parameters date range.


Personally, given the above, I could never get a &lt; &gt; solution to work. The only way I could get it to work properly and not miss anything is by using a CTE and saying where d.[Date] BETWEEN t.StartDate AND t.EndDate.

I hope this clears things up. Thanks again.
You can do steps 1 and 2: but use the DATEADD/DATEDIFF technique as per this: How to the get current date without the time part

You will most likely be unable to index the computed column because it won't be deterministic with the varchar method