fmpdude

Unexpected FMP Date Computation Issue

Discussion created by fmpdude on Apr 27, 2018
Latest reply on Apr 29, 2018 by fmpdude

Recently, we had a lively conversation on the forum about date math in FileMaker Pro 16. During this conversation, I found that FileMaker's date math doesn't match standard date API results (documented below) I used to calculate the date 1 month in the future from Jan, 31, 2000.

 

Here's what FileMaker gets (attempting date math directly with no CF):

 

Note: as others have pointed out below this date calculation approach above is "apples and oranges" to the methods described below. However, the goal, was to do date math for one month ahead of a date either directly (as the example above), without having to write a "CF", or using a built-in date function that abstracts that calculation....and get the same result as the standard date APIs below.

 

Below are my other tests (nine of them using different methods)....all of which show that adding a month to 2000-01-31 gives 2000-02-29, not, as FileMaker computes using the attempted direct date method above, 2000-03-02.

 

Test 1 and Test 2 (two different API tests):

Using Java, either the Java 8 DateTime API (LocalDate's "plusMonths()" method or the older Calendar class) gets:

2000-02-29

 

---

 

Test 3:

Additionally, going to a website that lets you calculate a date in the future, when picking 1 month, from 2000-01-31, gave me 2000-02-29:

 

 

----

Test 4:

Also, Excel, using the EDATE function to add 1 month to 2000-01-31 gave me .... 2000-02-29:

----

Test 5:

Using Apache's DateUtils common library, static method addMonths: DateUtils.addMonths() method gave me.... 2000-02-29

(DateUtils (Commons Lang 2.6 API))

 

----

Test 6:

Using Python's DATE API, we also got 2000-02-29 as one month from 2000-01-31.

----

Test 7: Visual FoxPro

returns...

----

Test 8: JavaScript's "Adjust" function

returns: 02-29-2000

 

---

Test 9: MySQL using DATE_ADD() function

Outcomes