Japanese Date formats

4 August, 2010 (23:34) | FileMaker, FileMaker Go

Hizuke

Many years ago, I saw a trick from Kevin Frank for entering dates in a system agnostic way (http://www.sumware.net/robfm2/?p=6)[look to the second post in this blog for a tale of the good old days].

The trick is to enter dates in FileMaker’s Japanese format, which is YYYY+M+D.

Brilliant! If you are inserting a calculated date into a date field then you don’t have to know anything about the file’s date format and therefore don’t have to make a decision to enter the date mdy or dmy.

If you want to find a range of dates, then simply enter yyyy+m+d…yyyy+m+d, substituting the start and end dates.

I used Kev’s trick in a few solutions that require reports for last week/last month/etc., using a calculated insert something like this:

Let ( [
   d = Get ( CurrentDate ) ;
   p = $searchPeriod ;
   s = Case (
      p = "Last Week";   d - ( DayOfWeek ( d ) - 1 ) - 7 ;
      p = "This Week";   d - ( DayOfWeek ( d ) - 1 ) ;
      p = "This Month";   Date ( Month ( d ) ; 1 ; Year ( d ) ) ;
      p = "Last Month";   Date ( Month ( d ) - 1 ; 1 ; Year ( d ) ) ;
      p = "Last Year";    Date ( 1 ; 1 ; Year ( d ) - 1 ) ;
      p = "This Year";    Date ( 1 ; 1 ; Year ( d ) ) ;
      "" ) ;
   e = Case (
      p = "Last Week";    d + ( 7 - DayOfWeek ( d ) - 7 ) ;
      p = "This Week";    d + ( 7 - DayOfWeek ( d ) ) ;
      p = "This Month";   Date ( Month ( d ) + 1 ; 1 ; Year ( d ) ) - 1 ;
      p = "Last Month";   Date ( Month ( d ) ; 1 ; Year ( d ) ) - 1 ;
      p = "Last Year";    Date ( 12 ; 31 ;Year ( d ) - 1 ) ;
      p = "This Year";    Date ( 12 ; 31 ; Year ( d ) ) ;
      "" )
   ] ;
   Year ( s ) & "+" & Month ( s ) & "+" & Day ( s ) & "..." &  Year ( e ) & "+" & Month ( e ) & "+" & Day ( e )
)

That solved a problem of the script failing on US systems!

Go

This also works on FileMaker Go, which sort of doesn’t surprise me, but does re-inforce the depth of the FileMaker Go product!

PHP API

It also seems to work nicely with the FileMaker PHP API. If my understanding is correct, the PHP API leverages the XML interface on FileMaker Server. Dates are mandated in the XML documentation to be in MDY format (I can’t say the same about the PHP API, as the documentation, I find, is quite sterile and lacking in examples).

On a hunch, I tried the japanese date trick on a PHP form and it too, worked as expected. I want to test this more, but I’m hoping it is robust and supported, because that simplifies some of the stupid date manipulations you have to do when handling dates with PHP :)

Postscript: Kevin tells me he learned this trick from (the venerable) Sean Flisakowski posting on FM Forums. Sean used to be on the FileMaker team and then moved to Apple.

Write a comment