Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
Daniel Nashed – 25 June 2019 04:29:49
I ran into an issue with my delete log application when converting timedates from the internal format like [20190612T154744,66-05] to a date when the server is in the US. What I have assumed is that if I specify a dot instead of the slash the @TextToTime function would work with day.month.year.
But it appears it is still working month.day.year instead.
Does anyone have an idea how to do this conversion operation safely internationally?
Is there any save string format which will always corrected in the same way?
In C-API it would be simple. You can fill in the corresponding values in the TIME structure and convert.
But I did not find anything like that in @Formulas or LotusScript.
Before I start spending time on it, probably someone has solved this already ...
-- Daniel
- Comments [8]
1Marco Bienlein 25.06.2019 4:58:40 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
Hey Daniel,
in Formula, I also split the Time String into it‘s fragments and then, after converting into integers, I use the @Date function for conversion into datetimes...
Marco
2Daniel Nashed 25.06.2019 5:13:39 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
@Marco, that does not help to solve my international issue. Unless the @Date and @Time function works differently.
I am not using integers. I am getting the string parts together and add the dot as the delimiter.
-- Daniel
3Werner Götz 25.06.2019 11:52:31 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
Hi Daniel,
I think Marco is right ...
Try something like:
s := "[20190612T154744,66-05]";
t := @ToNumber(@Middle(s; 1; 4) : @Middle(s; 5; 2) : @Middle(s; 7; 2) : @Middle(s; 10; 2) : @Middle(s; 12; 2) : @Middle(s; 14; 2));
@Date(t[1]; t[2]; t[3]; t[4]; t[5]; t[6])
Be aware: the formula above does neither regard the timezone (-05) nor the milliseconds ...
Best regards
-Werner
4Paul Withers 25.06.2019 13:04:33 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
I'm not sure about formula language, but I recently hit a similar problem for OpenNTF Domino API. The NotesInternational class has IsDateDMY, isDateMDY and isDateYMD. You'll also need the DateSep property as well. If you're using the time IsTime24Hour will also be relevant. NotesSession.International gives you an instance for the current server. This might be of use https://github.com/OpenNTF/org.openntf.domino/blob/master/domino/core/src/main/java/org/openntf/domino/impl/Session.java#L2274
5Daniel Nashed 25.06.2019 13:26:42 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
@Werner, thanks!
I would not have asked if the solution would be that easy.
The problem is that depending on the locale setting the month and the day needs to be switched to a different poition.
It's not about the string operations needed to to bring it in to the right format.
The date is already in the right timezone. The zone is just for information. And that wasn't part of my question.
The challenge is the conversion itself.
I was looking for a string format where the formula would always understan the format correctly.
But the dot does not make it automatically a date that is in day.month.year format.
Pauls post goes into the directly that I thought of as a work-around.
I thought there would be an easier solution that to check if the date string needs MDY or DMY format.
My string format is fixed. That's what I get in the deletion log. If I cannot find a string format that works with the function, what Paul is describing seems like the only solution/work-around.
Maybe someone comes up with a string format that is savely working for all regions. That would be the best solution.
Else I will read the settings for DMY and the separator and build the string for the region the code runs in.
-- Daniel
6Werner Götz 26.06.2019 5:43:59 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
Hi Daniel,
then I think i did not understand the problem ...
I thought your strings contain the timedates always in format "[yyyymmddT...]"?
In that case the formula above always (!) converts the string to the right "NotesDateTime" (date + time) - without dependence on DMY / MDY !?!
-Werner
7Daniel Nashed 26.06.2019 6:08:40 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
Ohhh Werner is right. I was on the road yesterday and I was too focused on the first part of his formula that I did not realize that the @Date function used in his formula allowes to specify the parts of the date separately.
Also because the conversion was done in a list and I missed that part of the conversion when first looking into it. Doing the conversion to integer in one step is also a great idea. This saves the separate calls for each component. But that caused me not to see it at first look.
This should solve the format issue! And this should work in all cases with all date formats!
In Lotus Script there is a DateNumber and TimeNumber function which does only do it separately but could be combined.
The @Time function in @formulas does the same than the @Date function and also allowes to specifc Date and Time in contrast to Lotus Script.
Huge thanks! This should solve it!
Daniel
8Paul Withers 27.06.2019 7:32:38 Date Conversion Question Lotus Script / Fromulas @TextToTime Internationally
In LotusScript there's the SetAnyTime and SetAnyDate functions to make them date only or time only. One impact of these is that they don't have a timezone associated as a result. So with SetAnyDate you're specifying a specific time, not a time in a specific timezone. See https://www.ibm.com/support/knowledgecenter/en/SSVRGU_9.0.1/basic/H_SETANYTIME_METHOD.html and https://www.ibm.com/support/knowledgecenter/en/SSVRGU_9.0.1/basic/H_SETANYDATE_METHOD.html