Sign in to follow this  
Followers 0
toms

Extracting date and time from 'timestamp'

5 posts in this topic

Hi

I would like to extract date and time from a timestamp field.

Are there any more 'elegant' methods than using left/right/middle string functions?

Thansk for interest and suggestions

Share this post


Link to post
Share on other sites

For time use

GetAsTime ( RightWords ( GetAsText ( TS ) ; 1 ) )

For date use

GetAsDate ( GetAsText ( LeftWords ( TS ; 1 ) ) )

TS is the timestamp field

Share this post


Link to post
Share on other sites

GetAsDate ( TimeStamp Field )

and

GetAsTime ( TimeStamp Field )

Share this post


Link to post
Share on other sites

Thanks for advices. Both methods work fine.

My problem was, however, due to an improper format of the imported timestamp field.

I have one further question. Does 'GetAsDate' read the date only from DDMMYY-format timestamp? (It seems so).

In my solution I import data which contains a timestamp field formated MMDDYY. I have already written a calculation formula converting the date to DDMMYY format. But perhaps it is possible to read it 'natively'?

Regards

Tomek

Share this post


Link to post
Share on other sites

Filemaker's native date is a serial number. To display the date, Filemaker uses the date format of the operating system.

The GetAsDate() function works in the opposite direction. It takes text and tries to convert it into a valid Filemaker date. This will only work if the text is a date formatted in the date format of the operating system.

MMDDYY is NOT a date or timestamp - it is text. Unless your operating system is set to use MMDDYY as its date format, GetAsDate ( MMDDYY ) will not work.

If your imported data has a text field in the format MMDDYY, and your operating system date format is DD/MM/YY (note the separator!), then you can use the calc (result is Date) =

GetAsDate (

Middle ( text ; 3 ; 2 ) & "/" &

Left ( text ; 2 ) & "/" &

Right ( text ; 2 )

)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0