Converting text with GMT offset to timestamp

[TBaker]

Hi,

I'm pretty new to Filemaker and was wondering if you might be able to help me out with this. I need to convert a timestamp in this format- Sun Jun 03 2012 03:13:28 GMT-0700 (PST) into something that Filemaker would recognize (correct me if I'm wrong, but it needs to be in MM/DD/YYYY HR:MM:SS PM to import, and then you can display it in any other format afterwards, right?). I've looked around the forums, but having issues with the GMT offset & the month being letters instead of a number. Any help would be much appreciated, thanks!

[eos]

The following calculation will convert a text string in your format (DAY MON DD YYYY HH:MM:SS) into a string which GetAsTimestamp ( ) accepts. You can then display it as timestamp or as date only or time only.

Note that, AFAICS, the acceptable date format seems to be dependent on the system settings, not on the format Filemaker uses for constructing a date with the Date ( ) function, which is always MM ; DD ; YYYY, regardless of regional time settings. Therefore, you will need to use either MM/DD/YYYY (US) or DD/MM/YYYY (Europe). Here's an example:

Let (

[ field = "Sun Dec 18 2012 03:13:28 GMT-0700 (PST)" ;

myMonth = ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Middle ( field ; 5 ; 3 ) ; 1 ; 1 ) + 2 ) / 3 ; // courtesy of Ray Cologon

myDay = Middle ( field ; 9 ; 2 ) ;

myYearAndTime = Middle ( field ; 12 ; 13 ) ;

// myString = myMonth & "/" & myday & "/" & myYearAndTime ] ; /* US format */

myString = myday & "/" & myMonth & "/" & myYearAndTime ] ; // European format

GetAsTimestamp ( myString )

)

= 18/12/2012 03:13:28 on my system (German date settings); the alternate format yielded a ?, since GetAsTimestamp doesn't accept 12/18/2012 03:13:28 as a valid date in German format. Just remove/add the leading comment slashes from/to the line you want or don't want to use, respectively.

Decide what to do with the GMT offset. You can't store this datum with the Timestamp itself . A timestamp is nothing but a number (of seconds), which you can retrieve (with GetAsNumber ( )). If you calculate the number of seconds the offset represents, you can add or subtract this number to/from the timestamp; this approach has the added benefit that both time and date will be adjusted.

[comment]

The following will convert the text into a Filemaker timestamp containing the GMT. We need more rules or examples regarding the timezone offset. For example, will there always be a "GMT" string preceding the offset? What if the offset is positive - will there be a + sign?

Let ( [

mmm = MiddleWords ( text ; 2 ; 1 ) ;

string = "janfebmaraprmayjunjulaugsepoctnovdec" ;

m = Ceiling ( Position ( string ; mmm ; 1 ; 1 ) / 3 ) ;

d = MiddleWords ( text ; 3 ; 1 ) ;

y = MiddleWords ( text ; 4 ; 1 ) ;

t = MiddleWords ( text ; 5 ; 1 )

] ;

Timestamp ( Date ( m ; d ; y ) ; t )

)

[TBaker]

Thank you both very much, that worked great!