FileMaker Date Fields Explored
AHH, DATE FIELDS. On the surface, one would think date fields are pretty strait-forward. But there are twists and turns when dealing with date fields, and it’s best to know FileMaker’s rules when working with them:
The date field is designed to hold a data string representing a single date such as 11/22/2004. When you define a field as a date field, you are making a commitment to that data type. In other words, you don’t enter text in a date field, you don’t enter numbers in a date field and you don’t enter a time in a date field. If you were to try, FileMaker will bark at you that it does not recognize the data string as an acceptable date value…
Although FileMaker will accept 2 characters for the year information ( such as 04 for 2004 ), it is not recommended that it be entered that way. If your database holds any historical date information ( like a birth date or the date that a building was erected ), FileMaker could interpret 30 as 2030 and not 1930. What FileMaker will do is look at the 2 digits and guess what 4 digits you really want. If the 2 digit year is within 30 years of the current year, it will assume you mean in the future or the past 30 years. If the two digits is greater than 30 from the current 2 digit year, FileMaker will assume you are talking about a date in the past.
For example, if today is 10/30/2004 … a date entered in as 01/01/01 is recognized at 01/01/2001 … a date entered in as 01/01/25 is recognized at 01/01/2025… a date entered in as 01/01/45 is recognized at 01/01/1945.
Dwayne Wright’s post covers all of the basics of what is allowed and what isn’t, and the assumptions made be FileMaker. The next step is doing calculations with dates, which offers it’s own tricks.
For example, subtracting 1 from a date (9/11/2014 – 1) will take it back a day (9-10-14).
Subtracting one date from another, 3/25/14 – 3-20/14, for example, returns -4.
And there’s lot’s more. Time to hit the books and learn all you can about handling dates.
The moral of the story? What seems simple and strait-forward isn’t always what it seems.
via Dwayne Wright PMP, PMI-ACP, CSM – FileMaker Thoughts – FileMaker Date Fields Explored.