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

I hasve now suggested 3 times that SQL Server (not VFP) stores a datetime as 2 integers. I have now confirmed that the first is an offset for date on or after base date. The second is ticks since midnight.

When you index a datetime in SQL Server, this is what you are indexing.

Casting: when a datetime is compared to a string value per common practice, a CAST is needed to compare a datetime field's 2 integers to the string. SQL Server and Sybase before it knows to convert the string to internal datetime format at the start of the query which happens only once, so low cost - and can make use of the datetime index.

If you use LIKE with datetime, there is no way to avoid a CAST for every row that meets other conditions, to evaluate this condition. Apart from the implicit cast defaulting to something like 'Sep 7 2018 5:30AM' which is poorly equipped for use in a LIKE for date range calculation, this is much higher cost. You insist that still it might be possible to use the index on datetime: but how? Unless you also index on whatever implicit CAST format SQL Server is configured to use (which by the way could use multiple languages for the month name that starts the string), how can string casting/comparison use an index on a 2 integer internal format?

If you know how, why not answer rather than suggesting other people haven't read the thread or are getting confused with VFP tables?

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

In my very first response I showed how easily this is managed if you use parameters. E.g. in VFP:

ltUpperLimit=dtot(date()+1)-1
? m.ltUpperLimit

Voila, an inclusive upper parameter for today's date. As Dragan notes, you might want to add the rest of the ticks stored in SQL Server's second integer for datetime- but I know they're never used in my data and there's value in standard parameterized SQL that works the same against SQL Server, Oracle or other databases.