Brian Dunning's FileMaker Custom Functions

time.Convert ( TmStamp ; Offset ; FromForm ; ToForm )

Converts a timestamp from one format to another

  Average rating: 4.3 (39 votes) Log in to vote

Rob Poelking   Rob Poelking - Show more from this author
Excelisys, Inc.
http://excelisys.com

Share on Facebook Share on Twitter

  Sample input:
time.Convert ( "06/23/1967 4:33:26 PM" ; "0" ; "FM" ; "sql" )
time.Convert ( "1967-06-23 16:33:26" ; "0" ; "SQL" ; "unix" )
time.Convert ( -79687594 ; "0" ; "unix" ; "xml" )
time.Convert ( "1967-06-23T16:33:26-05:00"; "" ; "xml" ; "fm" )
  Sample output:
1967-06-23 16:33:26
-79687594
1967-06-23T16:33:26-00:00
6/23/1967 9:33:26 PM

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Converts the time stamp formats of FileMaker, XML (ISO 8601), SQL or Unix into another format by specifying the FromForm and ToForm. You can convert a time stamp in unix to xml or FileMaker to SQL.

 

Comments

Chris McCue   Chris McCue, Dallas, TX
Sep 3, 2013
Great function! Moving records back and forth between FileMaker SOAP web services and MySQL tables is made much simpler with this handy calculation.
 
Christos Savva   Christos Savva, Cyprus
Jul 13, 2017
There is an issue with XML format for Eastern Hemisphere times.

This line:
sg = If ( sgn > 1 ; "+" ; "-");

Should be
sg = If ( sgn = 1 ; "+" ; "-");

Otherwise you never get +ve timezones.
 
Jason Wood   Jason Wood, Define Database
Mar 19, 2019
The example
time.Convert ( "1967-06-23T16:33:26-05:00"; "" ; "xml" ; "fm" )

Returns a "?" for me in FileMaker 17...
 
Rob Poelking   Rob Poelking, Excelisys, Inc.
Mar 20, 2019
Thank you, Jason. I had made a recent change to the function and I am using it in FM 17. I just input that same string and got back the correct form: 6/23/1967 9:33:26 PM. Note that (at least for xml) it is correctly evaluating the time offset of 5 hours.
 
Jason Wood   Jason Wood, Define Database
Mar 21, 2019
Hi Rob, I believe it's because of my Canadian localization, where dates are YYYY-MM-DD. I found another solution so no worries about fixing for me but I do think it's likely there is a dependency in here relating to the system format.
 
Jason Wood   Jason Wood, Define Database
Mar 21, 2019
Found it... if you are outside USA, you would have to update this line:
FMTSFormat = "mm/dd/yyyy hh:nn:ss" ;
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth:

Under construction. Email me your wish list for improvements.