Date/Time translation

[rlinsurf]

I have the following string:

Sun, 07 Jun 2009 08:55:46 GMT

I need to convert this into date and time format. Is there an easy calc to accomplish this?

Edited October 18, 2009 by Guest

[Vaughan]

If it's *always* in that format then it should not be too hard. Look at the words in the string

2 - day

3 - month name

4 - year

5 - hour

6 - minute

7 - seconds

You'll need a bit of self-assembly to convert the month name into the month number, a case statement will do it.

For the time, it's in GST so you'll need to assemble the hh:mm:ss together then add or subtract the time zone offset from it to get the local time (if the local time is what's needed).

The calc to create a date or time would look something like this:

Let( 



[

text = fieldname ; 

dy = middlewords( text ; 2 ; 1 ) ; 

mname = middlewords( text ; 3 ; 1 ) ; 

mon = case( mname = "Jan" ; 1 ; mname = "Feb" ; 2 ; ... ) ; 

yr = middlewords( text ; 4 ; 1 ) ; 

hr = middlewords( text ; 5 ; 1 ) ; 

min = middlewords( text ; 6 ; 1 ) ; 

sec = middlewords( text ; 7 ; 1 ) ; 

zone = 0 ; /* <- insert the time zone offset hours here */



tmestmp = Timestamp ( Date( mon ; dy ; yr ) ; Time( hr ; min ; sec ) + zone ) ;



]



Date ( tmestmp ) 

// Time( tmestmp )



)

Note that the Case() for the month names isn't complete.

Edit: just realised that the calculation to account for the time zone may need to affect the date as well as the time. The calculation has been changed to account for this.

Can somebody check the logic behind this and confirm it works for times around midnight?

Edited October 19, 2009 by Guest
allowed for edge case around midnight

[LaRetta]

Well, I don't see the need for accounting for the time zone but I may be wrong.

It seems it would be THEIR time zone regardless of what that is. And, because it doesn't list AM or PM, it should be military time. What I came up with is:

Date calculation (result is date):

Let ( [

m = 1 + Div ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; MiddleWords ( text; 3 ; 1 ) ; 1 ; 1 ) ; 3 ) ;

d = MiddleWords ( text; 2 ; 1 ) ;

y = MiddleWords ( text; 4 ; 1 )

] ;

Date ( m ; d ; y )

)

Time calculation (result is time):

MiddleWords ( text ; 5 ; 1 )

[rlinsurf]

These were incredibly helpful. Thanks to you both :