>>>>>A LIKE search do benefit from an index on a date\datetime\datetime2\datetimeoffset column.
>>>
>>>If you're right re datetime which is what the thread is about, I'm very surprised since datetime is not stored as a string by SQL Server. i understood that LIKE on anything except a string, must involve implicit CAST to string that won't benefit from an index in a different format. But as I said earlier, I don't see how LIKE does anything for datetime you can't do with standard conditions, so I'm happy to leave it there.
>>>
>>>>>Mind you, you cannot use BETWEEN queries for datetime range searches in MS SQL server.
>>>
>>>So I must by lying that I've been doing it since last century? Along with quite a few others who also describe doing this online? Whatever, Cetin.
>>
>>There IS an implicit casting. But having an implicit cast doesn't mean that an index wouldn't be used (I think you were thinking VFP tables).
>>About BETWEEN, I didn't say you are lying, just maybe you weren't aware of that your searches were returning results (correct?) just by chance. I have demonstrated that a zillion times here and elsewhere. With BETWEEN range boundaries are INCLUSIVE and with MS SQL server datetimes there is no way to create an INCLUSIVE upper bound.
>
>Solved that back in 2003 by going through all the dialogs where reporting parameters were taken, and replaced every date range like this
>
>between '2003-03-03' and '2003-04-03'
>
>became
>
>between '2003-03-03 0:0:0' and '2003-04-03 23:59:59'
>
>Nowadays I guess I was wrong, the latter should have ended with 23:59:59.999.

'2003-04-03 23:59:59' means you are missing the data entered in the last second, probably with a search like this they would never be listed.
'23:59:59.999' wouldn't work either because datetime is stored as 2 integers where second integer is ticks since midnight and a tick is 3.33 milliseconds. Thus that would also count entries belonging to next day.
As I said, in MS SQL server there is no way to express a datetime datatype range using BETWEEN.