How can I convert this timestamp into a FMP timestamp?

[HALBURN]

I have imported a bunch of data from a third party that has all the timestamps formatted like this: 2014-01-17 17:08:54

How can I convert it into a timestamp that FIleMaker will recognize?

Edited May 21, 2015 by HALBURN

[eos]

How can I convert it into a timestamp that FIleMaker will recognize?​

Parse out the components and pass them to Timestamp() – namely:

Let (
  ts = YourTable::yourField ;
  Timestamp (
    Date ( Middle ( ts ; 6 ; 2 ) ; Middle ( ts ; 9 ; 2 ) ; Left ( ts ; 4 ) ) ;  
    RightWords ( ts ; 1 ) 
  ) 
)

[HALBURN]

thanks eos!

[Jeff M]

Looks like a standard SQL timestamp format, should import into a FileMaker timestamp field correctly as is.

[comment]

Looks like a standard SQL timestamp format, should import into a FileMaker timestamp field correctly as is.

​It will "import into a FileMaker timestamp field correctly as is" only if the Filemaker file is using YYYY-MM-DD as its date format.

Edited May 21, 2015 by comment

[Jeff M]

​It will "import into a FileMaker timestamp field correctly as is" only if the Filemaker file is using YYYY-MM-DD as its date format.

​I'm not familiar with this, how do I check what date format the FileMaker file is using? Do you mean the formatting for the particular field?

[comment]

​Do you mean the formatting for the particular field?

​No, I mean the date format in use by the file. This is the format which must be used when entering dates (regardless of how a date field is formatted to display). It can be either the short date format of the OS under which the file was created, or the short date format of the current OS - see: http://www.filemaker.com/help/14/fmp/en/html/create_db.8.7.html

[Jeff M]

Awesome, thanks for the info! I don't know that I've ever changed that from the default, but good to know it's there. One more question about that if you don't mind... when using something like the get server timestamp function, does it consider the computer hosting the solution as the current OS? Any reason to be concerned about getting back improperly formatted data from FMS?

[comment]

That's a good question. I am afraid I don't know the answer, and at this moment have no way of finding out.