none
Date comparison independent of Regional Settings RRS feed

  • Question

  • I have date and time information as a number of integers: YYYY, MM, DD, HH, MM and SS

    I want to compare that with the PC-time using Now() and DateDiff

    I have a routine that creates a DateTime value based on the integers and then I use that in DateDiff

    My problem is that Regional Settings on the PC affects the date-formatting of Now() and thus the result of the DateDiff.

    How do I make a date format that always works - irregardles of the regional settings?

    Friday, April 1, 2016 8:00 AM

Answers

  • What method are you using to build your date/time from the integers?  Have you tried the DateSerial function?

    dt = DateSerial(intYear, intMonth, intDay)  

    This will return a DATE rather than a string, which should allow for region independent comparisons with other dates, including the Date() function.

    For comparisons including time components, such as comparisons with Now(), there is a corresponding TimeSerial function. 

    dt = dateserial(intYear,intMonth,intDay) + timeserial(intHour, intMin,intSec)


    Miriam Bizup Access MVP

    Friday, April 1, 2016 9:05 AM

All replies

  • What method are you using to build your date/time from the integers?  Have you tried the DateSerial function?

    dt = DateSerial(intYear, intMonth, intDay)  

    This will return a DATE rather than a string, which should allow for region independent comparisons with other dates, including the Date() function.

    For comparisons including time components, such as comparisons with Now(), there is a corresponding TimeSerial function. 

    dt = dateserial(intYear,intMonth,intDay) + timeserial(intHour, intMin,intSec)


    Miriam Bizup Access MVP

    Friday, April 1, 2016 9:05 AM
  • An alternative is to format your dates as yyyymmdd on BOTH sides of the comparison. eg:

    WHERE Format(DateField, 'yyyymmdd') >= format(Date(), 'yyyymmdd') 

    But imo, it is best to compare dates as dates rather than strings whenever possible.


    Miriam Bizup Access MVP

    Friday, April 1, 2016 9:09 AM
  • Thanks.

    DateSerial and TimeSerial seem like the way to go!

    I create a string based on the values - and that can cause trouble.

    In general - regional settings suck... there should always just bu one way of presenting values internally!

    Friday, April 1, 2016 9:43 AM