Level: Intermediate

GetAsDate Head-Scratcher

Recently I was doing some FileMaker training with my older son; we were looking at the “DayOfWeek” function in the data viewer, and I showed him that…

DayOfWeek ( Date ( 1 ; 22 ; 2012 ) )

…returns a 1, since this particular date falls on a Sunday, and FileMaker considers Sunday to be the first day of the week. Then I said, “Of course I could instead express it like this…”

DayOfWeek ( GetAsDate ( "1/22/2012" ) )

…and was nonplussed when a ? was the result.

Then I noticed that BaseElements was my active file, exclaimed “Those @#$@#$ upside down and backwards Australians!”, closed BaseElements, and voila, a 1 as nature intended (here in the US, at any rate), plus an opportunity to introduce the concept of regional settings.

The bottom line is that at the moment a FileMaker file is created, either the standard way, or on first opening a freshly-created clone (i.e., a file that was generated via “Save A Copy As Clone”), the current computer’s regional date format becomes associated with that file. And when it comes to the GetAsDate function, nothing you do in File Options…

…or via the Set Use System Formats script step…

…makes a bit of difference.

Recommendation: whenever possible, construct dates using the Date() function, which unambiguously takes three arguments in this order: Month ; Day ; Year.

8 thoughts on “GetAsDate Head-Scratcher”

  1. Alas I believe it is those #$%! backwards americans who are all wrong, what with their mm/dd format and their 5280 feet in a mile – everyone has different sized shoes!

    1. It seems that ISO8601 mandates YYYY-MM-DD, which generated some discussion in the comments at the bottom of the page here a few months ago, in connection with “Japanese” date formats.

  2. It is always instructional when the professor’s plans go awry, leading to more questions and another topic; almost as if it was planned. And that leads me to comment that, although “Save a Copy As Clone” creates a new FMP file, it does not set the Date Format (and other settings); the Date Format is set to the computer’s system settings when the Clone is first opened.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.