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
Kiza Solutions
https://kizasolutions.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, Kiza Solutions
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" ;
 
caitlin   caitlin
Sep 24, 2020
Jason Woods - thanks for the heads up - I'm using this, and would have driven myself crazy if I didn't read your comment!
 

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 16 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: