This solution is already on this thread (see Sergey's reply). However this part do not even need to be fast, it's just one call before the actual select. Key point is that select body do not have any function calls and could utilize an index on datefield column.
PS: I timed the one I proposed today and in 122+K records result was 0-15milliseconds. 0 was due to caching, 15 is before being cached. Sergey's should give the almost the same timing.
Cetin
>Itzik Ben-Gan outlined three methods in the Feb. 2007 issue of SQL Server magazine. His preferred method is this:
>
>
>
>SELECT DATEADD(day,DATEDIFF(day,'19000101',GetDate()),'19000101')
>
>
>
>While it was not quite the fastest it was up there with another solution that is not in this thread. This solution he said was twice as fast as the others on 1,000,000 iterations.
>
>
>
>>Consider the following select statement:
>>
>>SELECT [Id]
>>FROM [MyTable]
>>WHERE YEAR(DateField) = YEAR(@DateField) AND
>> MONTH(DateField) = MONTH(@DateField) AND
>> DAY(DateField) = DAY(@DateField)
>>
>>
>>I want to select the records that have the same date as the parameter, but I do not care about the time part of the field or parameter.
>>Is there another (better) way of doing this?
>>
>>Thanks,
>>Einar