Comparing Dates in FileMaker

Leave a comment

5-7-2008 by Jesse Antunes

Comparing dates can be confusing. It’s tough to tell exactly when FileMaker will treat the values that you are trying to compare as dates or as numbers or as…something else. Hopefully, I can point you in the right direction.

There are three ways that people commonly use to compare dates. One’s bullet proof, one’s pretty decent and one just plain doesn’t work. For the purpose of this exercise, I’m going to assume that there is a valid date in a field cleverly named MY_DATE and the MY_DATE field is indeed defined as a date field. Let’s start with the faulty comparison first:

 MY_DATE < 4/22/2008

Looks like it might work but unfortunately doesn’t work at all. What’s happening here is that FileMaker actually views 4/22/2008 as a mathematical expression. FileMaker sees something completely different than what you and I see.

We see:

Calender

FileMaker sees:

 4 / 22 / 2008 = 9.05469032959e-05

Tha’ts / for “divided by.” Whoops… Not quite what we are looking for. You can’t just put numbers and slashes together “in the raw” in a FileMaker calculation like this.

The next more common comparison is the following:

 MY_DATE > "4/22/2008"

Ding, Ding, Ding we have a winner. Well, sort of. We’ve told FileMaker to look at 4/22/2008 as a string this time. Since FileMaker is pretty smart about evaluating strings, it will take the locale information stored in the database and parse our string into a date. Since you are probably in the US and the locale in your database is set to the US this will work just fine.

There is actually a more explicit way of this same comparison but it’s essentially doing the same things as above:

 MY_DATE > GetAsDate( "4/22/2008")

Note: This is a little bit of an oversimplification. The locale information on a database is stored when the database is VERY first time the database is opened. We’re talking once that new database button was clicked or the first time you opened a saved clone. So if your annoying cube mate decided to set your computers locale info to Turkmenistan just before you created a FileMaker database, then congratulations; Your dates are now stored in whatever date format they happen to use. (Disclaimer: Turkmenistanian format may not be supported. I have no idea all the date formats that are supported but I’m sure it can be looked up)

Unfortunately for us, not everyone in the world uses the US date format. For instance England uses DD/MM/YY… wierd. I bet they don’t even use feet and inches either. Anyway, with their format our calculation would be looking for the 4th day of the 22nd month of 2008. So if you cloned your system and sent it to that British friend of yours, this comparison will most definitely fail. Probably not a big concern to most of us but hey, maybe you want to be really thorough.

Finally we have the bullet proof comparison.

 MY_DATE > DATE( 4 ; 22 ; 2008 )

Thank the FileMaker gods for the Date( ) function! Probably one of the most under appreciated functions in FileMaker, this bad boy takes care of all of the funny stuff for you. It’s definition is Date( month ; day ; year ) which is quite convenient if you are in the US because we are used to making dates in month/day/year format anyway. The Date() function will return you a real, live date value, no matter how your computer is configured. It’s the perfect way to compare dates.

Interesting Tidbit: FileMaker stores the dates you enter as a number. Each date is simply the number of days since 1/1/0001.

11 Comments

  1. Tom Fitch

    This applies to time and timestamps as well. Recently I was getting some weird results trying to construct a TS using GetAsTimestamp() when I suddenly realized the easy/foolproof way: the Timestamp() function.

  2. O Homan

    Actually, this is probably an issue where Filemaker (and many other programs as well) can improve a lot. The standard US date format for most software is a bit silly. After all, more than 95% of the world’s population lives outside the USA, including those in England (yes, they still use feet and inches, too). Here in the middle of Europe, my Filemaker still refuses to accept the local dd.mm.yyyy format for FIND commands but insists on using mm.dd.yyyy dates, even though the operating system has the correct locale defined. Why does Filemaker need to redefine the locale again, why can’t it take the OS locale?

  3. gessie

    Weird!! – wot’s weird??
    What’s weird about: the first day of the fifth month of 2008?? Nothing at all it’s logic is inherent in the progression from particular to general!
    What IS weird is what the Americans like to foist upon the rest of us – Month (Four) Day (Twenty) Year (Two Thousand and Eight) – which just flip-flops around – but they still seem to think they live at the centre of the universe and everyone else is weird!
    Personally, when it comes to sorting lists of dates properly, which I have to do all the time for film titles, I much prefer the Japanese system: Year; Month; Day – which progresses logically from the general to the particular. Then at least you get a properly sorted list according to date!
    @Homan – Ah well, I can’t imagine Filemaker Inc. ever going for local customs – so I can forget them ever doing it using a properly logical system from the start!!
    NaH Jesse – I like your post – just not your inbuilt prejudices. When were you first taught to think globally?
    gessie

  4. Rob Russell

    Don’t start me on date storage in FileMaker Pro :)

    Regardless of whether dmy (little to big – what most of the world uses), mdy (weird) or ymd (big to little – japanese) is the user’s system format or FileMaker Pro’s file format, there are other gotchas as well.

    One that recently came to mind is “getastext ( thedate )” which will always return the file’s format (defined by the system formats at creation time or when saved as a clone). If Geoff sends me a file on my New Zealand computer with Aussie dates, then getastext will likely return m/d/y, which is a pain if displaying a date in a string (such as a dialog).

    The hoops one must jump through to work out the user’s system formatting locale are not insignificant.

  5. Toby

    It appears to me that the author was injecting a bit of humor into his post (that is allowable, this is not an AP article after all), and not being prejudiced. I personally wish the US would go General-to-Specific or the reverse. I have lived my adult life with the US system and still don’t feel it is intuitive (maybe we can all switch to a new universal 10 base calendar and just start over). With that said, a US citizen is likely to write from a US perspective, just as much as any other citizen would write from their own perspective. Don’t attack the author for being helpful.

  6. Joe Vulgan

    Sorry to say, I am trying for the first time to evaluate dates and your suggestions are not working for me. Here is what I am doing:
    There are three fields:
    Date, Cost, Tax. I am trying to change the tax calculation base on the date. Doesn’t work for me in FileMaker pro 6.0 or in 10.0.
    This is what does not evaluate:
    Tax=If(Date<04/01/2009, Cost*.0875, Cost*.0975)
    I have tried all the variations in the above date discussion… I always get the result of 9.75 regardless of the date. What am I doing wrong?

    Thanks for the help,

    Joe

  7. Geoff Coffey

    Joe:

    Double check the article. The first few paragraphs are devoted to explaining exactly why what you are trying to do will not work.

    The next few paragraphs tell you how you should put dates in your calculation, and why.

    In a nutshell: You can’t put a bare date into a calculation like that. Instead, use something like this:

    if(Date < Date(04,1,2009), Cost*.0875, Cost*.0975)

    Geoff

  8. Joe Vulgan

    Geoff,

    Can’t thank you enough… I tried every delimiter except commas between the date numbers. Yes, of course it works now.

    Thanks a lot,

    Joe

  9. Steve

    All,

    I understand what this article is discussing but I am still at a loss with what to do with my problem:
    I have Filemaker Pro 6.0v1 running in a Xenapp5/W2K8 10 server farm (gutsy/stupid I know). The problem, in its simplest form is that one user can open the app and start a new DB from a template and they get mm/dd/yy while the user sat next to them does the same thing and gets dd/mm/yy. Upon further testing, I can log in and start a db from a template and get mm/dd/yy and then, in the same session, open a new db from no template, enter a date field, enter the ‘current date’ and get dd/mm/yy. I have changed the iDate registry value for HKEY_Users and HKEY_LocalMachine to 1 (dd/mm/yy). Some users work, some don’t.
    To top it off, the users that don’t work, used to. According to the previous engineer nothing was changed that could have concievably caused this.
    Help!!!
    Thanks for any assistance that you can offer.
    Steve

  10. Steve

    btw : The regional and language settings on all servers in the farm are set to Australian.

  11. Steve

    I have answered my own question after a bit more research. There are 3 reg settings that are needed to sort this issue out. Under HKEY_USERS\\Control Panel\International. iDate value needs to be 1, sCountry need to be Australia and sShortDate needs to be d/MM/yyyy.
    Thanks to me for my help!

Tell Us What You Think

*
* (will not be published)