Will different Windows Time settings mess a WHERE SQL Date statement in ACCESS?

user_15183533 0
Viewed 47

I know that ACCESS's time format depends on your Windows time settings. I use ISO-8601 format (YYYYMMDD) so that I can get away with SQL WHERE statements like this one:

WHERE dates > #2020/02/15#
  AND dates < #2021/01/30#

If I run the code from above in another computer, whose Windows time settings are for example DDMMYYYY, will the SQL statement no longer work? I could simply do something like this to solve that problem (will it though?):

  WHERE dates BETWEEN Format(date1, "\#YYYY\/MM\/DD\#") AND Format(date2, "#YYYY\/MM\/DD\#")

EDIT: Time format has beign changed as pointed out by @Gustav. The question remains; will the first WHERE Statement no longer work on different Windows time settings? Will the second correct the problem?

ms-access vba sql windows
2 Answers

In Access SQL, use octothorpes:

WHERE dates > #2020/02/15#
  AND dates < #2021/01/30#

WHERE dates BETWEEN Format(date1, "\#YYYY\/MM\/DD\#") AND Format(date2, "#YYYY\/MM\/DD\#")
user_35272970

Nope, Windows time settings will mess with a lot of things, but not with ordering or comparisons with dates.

As long as the field is defined as a date (so with octothorpes, like Gustav said), the 2nd of February 2021 will be less the 11th of February 2021, even though that wouldn't be the case if you cast them to a string first.

Always try to keep columns as they are when filtering, so if dates is actually a date column (and not a formatted string), just use WHERE dates BETWEEN #2020/02/15# AND #2021/01/30#, no formats, no funky stuff. And note that especially when trying to keep your application working in all locales, it's important to avoid casting dates to strings, which can happen if you compare a date with a formatted string.

user_72968930
Related