my 2 cents regarding the example of a date comparison using the <= operators:
The same effect can be experienced (perhaps unknowingly) when using the BETWEEN query operator with 2 dates as criteria when those fields contain the time component of the date. WHERE aDate BETWEEN DateTime1 AND DateTime2 will have the same result as what you outlined. My solution is usually to use the DateAdd function to increase the second date by some number of minutes or seconds, bringing the second date up to just before midnight on the second date. The alternative is to use >= with < where the second date is the date desired plus one day. The issue for me as been how to apprise the users that they need to add an extra day to their target date, or simply do it for them in the hopes they don't add another one because they're aware of the issue. I opted for the date add function since only the experts in the work order management system were aware of the anomaly; the common db user was not.
Last edited by Micron; 03-17-2017 at 09:32 AM.
Reason: spelin
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.