I try to convert a text string like
into a time stamp like
I found calculations for date only and time only but not for this.
The result of the Let() function above is already “putting it back together”.
It's a native function, no need for a custom one.
You may need to wrap your string in a substitute though:
GetAsTimeStamp ( Substitute ( 2016-09-21 19:37:43 ; "-" ; "/" ) )
I forget if that function handles dashes vs. slashes or not.
Thanks for your swift response.
I have a text field with the imported text string "text"
and a calculation field for the calculation of the timestamp "timestamp"
how should I write the calculation ?
GetAsTimeStamp ( Substitute ( YOURTABLENAME::YOURFIELDNAME ; "-" ; "/" ) )
Mike's solution is straightforward, native and will work as long as the text string that will be passed is in the same date format as the system where the calculation is executed. If your system date setting is YYYY-MM-DD, the function will work if the text string is "2016-09-21 19:37:43", but will fail if the text string is "09-21-2016 19:37:43".
If you are positive that all strings fed to your calculation will match the system settings, then you can rely on GetAsTimeStamp function (no need for the Substitute function by the way, the function handles both delimiters).
Yes, the format needs to be changed from yyyy-mm-dd in the text string to dd-mm-yyyy in the timestamp.
Indeed, dashes or slashes are not the problem.
How can I change the format ?
tsone = YOURTABLE::YOURFIELD;
tsonelist = Substitute ( tsone ; [ "-" ; ¶ ] ; [ " " ; ¶ ] ) ;
tsoneday = GetValue ( tsonelist ; 3 );
tsonemonth = GetValue ( tsonelist ; 2 );
tsoneyear = GetValue ( tsonelist ; 1 );
tsonetime = GetValue ( tsonelist ; 4 )
tsoneday & "/" & tsonemonth & "/" & tsoneyear & " " & tsonetime
That's essentially transforming this:
and then putting it back together in the right order.
........and then putting it back in the right order in a timestamp field.
How can I do that ?
I am sorry but my knowledge is very limited.
Is there a book for studying calculations and scripts ?
I would like to understand what I am doing.
Thanks for the link to the courses, I will work on them.
If use the calculation for a timestamp field I get
In a text field it works fine
Now I made a text field "time stamp txt" and next a timestamp field "timestamp TS" with the calculation
GetAsTimestamp ( time stamp txt )
That works nicely.
Can I include the last step in the first calculation to avoid the extra field ?
Apologies.... it works in a timestamp field as well.
Apparently, I made a mistake.
many thanks for your help.
I believe this incorrect.
the hard coded date would be text (quoted) or the field reference
the substitute wouldn't work with "/" as the string is YYYY-MM-DD
/* GetAsTimeStamp ( Substitute ( "2016-09-21 19:37:43" ; "-" ; "/" ) ) */GetAsTimeStamp ( Substitute ( "2016-09-21 19:37:43" ; "-" ; "+" ) )
/* GetAsTimeStamp ( Substitute ( "2016-09-21 19:37:43" ; "-" ; "/" ) ) */
GetAsTimeStamp ( Substitute ( "2016-09-21 19:37:43" ; "-" ; "+" ) )
Because FileMaker date & timestamps can be Japanese-formatted, the "+" will transform the date correctly.
surprised me, too!
Yes, just wrap the last line of the LET statement, where it assembles the TS back together, in GetAsTimestamp( … that line … )
I would add a row in that Let() statement, storing an intermediate date value, put together using the Date(month;day;year) function. (And of course doing something similar for time.) And then creating the timestamp using the Timestamp(date;time) function.
That way I be 100% sure that it will reproduce a valid timestamp regardless of what the "local time and date settings" might be on any future computer that might come in use.
With best regards Magnus Fransson.
Retrieving data ...