Converting Timedate Strings in @Forumula Language with different date formats
Daniel Nashed 11 February 2020 11:19:32
Maybe I am thinking to complicated.. But I did not find an easier solution ..
I had an interesting issue yesterday when adding multi timezone and date-time format to my backup solution, because I cannot store everything in Notes native TIMEDATES.
There are some text logs involved, when storing information about a backup. To ensure I have an unified format, I decided to change the internal storage to UTC (in Notes terms: GMT).
--------------------
Update 12.02.2020: Yes I was thinking to complicated, because I missed the function @Date which takes the date components as numeric values in a fixed order.
@Date( time-date )
@Date( year ; month ; day )
@Date( year ; month ; day ; hour ; minute ; second )
After feedback from Simon (huge thanks) and also with some discussions with my friend Rudi Knegt, who is also an old Lotus Formula fan like me, I now have the following solution to convert my date. I actually need the date as text and as time for comparison. That's why I did the conversion of the full date this way at the end. So my use-case need the text for selection and the date for comparison.
X:="2020.02.11 01:02:03 GMT"; Z:=" ";C:=@Explode(X;Z); D:=@ToNumber(@Explode(C[1];".")); @if(@Elements(C)<3;"invalid timedate";@Elements(D)<3;"invalid date";@ToTime(@Text(@Date(D[1];D[2];D[3]))+Z+C[2]+Z+C[3]));
By the way. Without knowing the other parameter options @Date function we had another idea taking benefit of the wrong text to date conversion. If you convert a date with the day>12 the result can be used to replace the result with the date format I would have needed.
That solution would not have needed the Lotus Script code in my first solution below. But with the @Date function it's more straightforward.
My final solution in my actual code looks a bit different and just needs one explode. But for demonstration purposes in this context it makes sense.
Thanks! This is true community spirit!
-- Daniel
--------------------
With my German locale I ran into limitations when converting the text back into a date.
It turned out that even when I use a string that is YMD formatted, the day/month order is still from my German locale and causes conversion issues for the first 12 days in a month.
This wasn't what I expected because there are those 3 different settings internally: DMY, MDY and YMD.
Example with German settings:
@ToTime ("2020.02.10 11:22:33 GMT") --> 02.10.2020 13:22:33
@ToTime ("2020.10.02 11:22:33 GMT") --> 10.02.2020 12:22:33
@ToTime ("2020.02.14 11:22:33 GMT") --> 14.02.2020 12:22:33
In my case my internal format is always "yyyy.mm.dd hh:mm:ss GMT" and I have to convert to a correct TIMEDATE.
I had to use Lotus Script in the DB init event to get the international settings and store them in an environment variable to be used in my form.
With that client specific setting, I am converting the date to the current settings. From there on I can use @ToTime to convert it correctly.
D:=@Word(x; " ";1);T:=@Word(x; " ";2);Z:=@Word(x; " ";3);@ReplaceSubstring(DateFormat; "Y":"M":"D":"T":"Z";@Word(D;".";1):@Word(D;".";2):@Word(D;".";3):T:Z);
If someone finds a more straight forward way to convert this given format with different international settings, I would love to make it easier.
Specially reading the international settings via Lotus Script and passing it to my formula is ugly ..
-- Daniel
Example:
With --> x:="2020.02.10 11:22:33 GMT"; DateFormat:= "D.M.Y T Z";
The RESULT is --> 10.02.2020 11:22:33 GMT
Form there on I can use @ToTime to convert it correctly independent from the date settings of the client...
DateFormat:=@Environment( "DominoBackupDateFormat");
Sub Initialize
Dim session As New NotesSession
Dim international As NotesInternational
Dim DateFormat As String
Set international = session.International
If international.IsDateDMY Then
DateFormat = "D.M.Y T Z"
Elseif international.IsDateMDY Then
DateFormat = "M.D.Y T Z"
Elseif international.IsDateYMD Then
DateFormat = "Y.M.D T Z"
Else
DateFormat = "D.M.Y T Z"
End If
Call session.SetEnvironmentVar( "DominoBackupDateFormat", DateFormat )
End Sub
- Comments [3]
![[HCL Domino]](../lotus-domino.gif){kind=small}
![[Domino on Linux]](../domino-linux.gif){kind=small}
![[Nash!Com]](../nashcom.gif){kind=small}
![[Daniel Nashed]](../daniel-nsh.gif){kind=small}