>>Similar to what I was doing a dozen years ago:
>>
>>
select * from MyTable where DateTimeCol <= '20170810 23:59:59'
>
>In a fast moving environment, that would miss the records entered after 23:59:59 (and it happened to me in real life, was a hard to catch bug).
What was the content in those records, assuming it was a datetime? For datetime2 it would obviously be
select * from MyTable where DateTimeCol <= '20170810 23:59:59.9999'
If the record was entered after 23:59:59, I'd assume the actual value would be rounded to whole seconds. Those in the first half would be rounded into the ":59" part, the rest would be in the next day. Unless it was the VFP5 with hidden milliseconds?
>Once upon a time Naomi made me aware of date range queries using BETWEEN - which uses <e; (thanks Naomi). Since then I realised the only reliable way is to use < operator for the ending value. Converting to date would work but that is an unnecessary overhead. With this approach, it works very well whether the type is a date, date time, datetime2 ...
Yup, handles those pesky fractions as well.