Comparing SQL timestamp to FM timestamp

[AgentsSka]

I am trying to compare the modification timestamp of records in a FM database, vs those in a SQL Database.

 

The FM timestamp is formatted:  6/13/2011 10:55:11 AM

 

The SQL timestamp is formatted:  2012-04-26 14:57:03

 

As a result running a calculation on the two timestamps fail.  It appears I have to format the SQL timestamp differently.

 

Does anyone know the easiest way to parse 2012-04-26 into values that I can use inorder to use the "Date" Function?

[jbante]

Try this:

Let ( [
~timestamp = "2012-04-26 14:57:03" ;
~timestamp =
Substitute ( ~timestamp ; [ " " ; ¶ ] ; [ "-" ; ¶ ] ; [ ":" ; ¶ ] ) ;


~year = GetValue ( ~timestamp ; 1 ) ;
~month = GetValue ( ~timestamp ; 2 ) ;
~day = GetValue ( ~timestamp ; 3 ) ;


~hour = GetValue ( ~timestamp ; 4 ) ;
~minute = GetValue ( ~timestamp ; 5 ) ;
~second = GetValue ( ~timestamp ; 6 )
];
Timestamp (
Date ( ~month ; ~day ; ~year ) ;
Time ( ~hour ; ~minute ; ~second )
)
)

If you do this frequently, you probably want to make a custom function out of it. In general, my first attempt to parse out pieces of delimited data in FileMaker is usually to substitute return characters for the delimiters, then use the GetValue function.

[AgentsSka]

Thanks.  I completely forgot about the substitute function.  That was perfect.