Domino on Linux/Unix, Troubleshooting, Best Practices, Tips and more ...

Converting Timedate Strings in @Forumula Language with different date formats

Daniel Nashed  11 February 2020 12: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

1Simon  11.02.2020 18:05:08  Converting Timedate Strings in @Forumula Language with different date formats

I have only used fixed date string and Used

dateParts := @TextToNumber(@Explode( reportStartDate ; "/" ));

@Date( dateParts[1] ; dateParts[2] ; dateParts[3] )

to Convert it back to a Date ignoring the Server/Workstation Formatting...

Hmm, if Adding Time - I am not sure which Timezone it would use...

2@Daniel Nashed  12.02.2020 9:03:31  Converting Timedate Strings in @Forumula Language with different date formats

@Simon THANKS!

The @Date function which is taking numeric date components in a fixed way was what I have been missing.

The only part where I need to be really careful is the number of elements in the arrary.

It is acutally really tricky to do the explode in this case, because that allows you to convert the text to number in one go as well :-)

-- Daniel

3Remco Angioni  12.02.2020 13:27:43  Converting Timedate Strings in @Forumula Language with different date formats

Like the great DELETIONLOG tool, can i sign in as a BETA tester for your backup solution?

Archives


  • [IBM Lotus Domino]
  • [Domino on Linux]
  • [Nash!Com]
  • [Daniel Nashed]