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] < @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 :(
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 ( < 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
< > 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
< > 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.