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.
4 / 22 / 2008 = 9.05469032959e-05
/ 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.