>>>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.