>>>You could simply create an index on a datetime field and then check the execution plans to observe that the index is used. I didn't think that you didn't know this, sorry.
>
>If you had read and understood the thread, you would know that I did this. (Addressing you back in the way you address others)

Um... this thread is now 16 days old, and we are all 16 days older than when it began... so let's assume that nobody remembers everything that's been said.

I never imagined I'd make a snowball in August without ever crossing the equator.

>Since this is in a VFP forum, reasonably you'd expect ticks to be 000. I didn't think that you didn't know this, sorry.

Not necessarily so. I had databases populated by other apps and my job was to access them (pun intended, the other frontend app was written in Access, 2004) from VFP and get things in order as much as possible. So VFP's datetime lacking ticks (amputated around VFP5-6) doesn't mean the ticks must be entirely absent.

>for VFP you could simply perform a BETWEEN using the supplied format to observe that datetimes with 000 ticks are returned by this query.

So you'd think there weren't any, and they may indeed be irrelevant VFP side. The question here is whether there were any records in the last second of the end of your time period which you should have retrieved but haven't.

>...
>
>so I should think you can safely use 997 ticks in upper bound of BETWEEN and catch them all.

Thanks for doing the legwork on what I speculated yesterday. And we don't care whether something happened in the last three milliseconds, because if that made any difference, the designer of the database should have used datetime2. As it is, it's either included in the current day (time portion not greater than 23:59:59.997) or belongs to the next day.

>Whereas for datetime2 with accuracy to 100 nanoseconds- I think I would use Jeff's less than bound every time to avoid worrying about all this. I think this is what he means about trouble with BETWEEN.

Let's err on the side of the benefit of a doubt (if I may be allowed to mess with phrases, it's a weekend) and assume that Cetin did have a real life situation where it mattered, and earned a few gray hairs over the problem. We all had such times.