Reg Exp
Web Design
Notes Client
Converting DXL dates to CDat format
If you've done any work with DXL, you have no doubt seen the "universal time" that is generated for date/time values. I wrote a little script function that will convert that date/time format into a LotusScript date/time value (I'm using the LotusScript date/time format - no time zone information).

As an example, let's take a look at a time value such as 20050913T140950,89-06. It starts out with "2005" - that's the year. Then comes "09" - that's the month (September - "01" would be January). Then comes "13" - that is the day of the month. "T" is a separator between the date part and the time part. "14" is the hour number. "09" is the number of minutes. "50" is the seconds. The comma is a separator for fractions of a second. My code ignores fractions of a second.

When you're working with agents in DXL, you can have just the time part (a daily agent is scheduled for a certain time of day). Those (values with only a time) will have a "T" as the first character. If there is not a "T", then it is assumed the value is only a date value (no time is present).

The true "universal time format" also includes a time zone. I have not seen time zones present in the DXL output files that I have created. The time zone will start with the "+" or "-" character (east or west of GMT) and include the offset. In true universal time format, the letter "Z" can be present to indicate that no time zone is given. This code will ignore all those possibilities. (One future enhancement could be to return a NotesDateTime object with the correct time zone, but since this code was originally written for DXL and I already mentioned that I haven't seen time zones in the DXL output, I chose to ignore any time zone values).

Function ConvertUniversalDateTime(value As String) As Variant
    On Error Goto BubbleError
    ' Take a string date and/or time value in "universal time" and convert it to a
    ' LotusScript date-time value. The value is NOT a NotesDateTime object
    ' value because time zones are not considered here.
    Dim datePart As String
    Dim timePart As String
    Dim dateTime As String
    ' If there is a "T", then both a date and time (or just a time) is present
    If Instr(value, "T") Then
      datePart = Strleft(value, "T")
      timePart = Strright(value, "T")
    Else ' no "T" - the value is only a date
      datePart = value
      timePart = ""
    End If

This first part of code splits out the passed-in value into its date piece and time piece. If there is a "T", then everything to the left of it (which could be nothing, if "T" is the first character) is the date part and everything to the right is the time part. If there is no "T", then there is no time part; only a date part.

    If Instr(timePart, ",") <> 0 Then timePart = Strleft(timePart, ",")
    If Instr(timePart, "Z") <> 0 Then timePart = Strleft(timePart, "Z")
    If Instr(timePart, "-") <> 0 Then timePart = Strleft(timePart, "-")
    If Instr(timePart, "+") <> 0 Then timePart = Strleft(timePart, "+")

This next part of the code gets rid of any fractions of seconds and any time zone information that is in the time part. We should be left with just the hours, minutes, and seconds.

    If Len(datePart) = 8 Then
      ' Convert the MM part to an abbreviated month name
      Select Case Mid(datePart, 5, 2)
      Case "01" : dateTime = "Jan "
      Case "02" : dateTime = "Feb "
      Case "03" : dateTime = "Mar "
      Case "04" : dateTime = "Apr "
      Case "05" : dateTime = "May "
      Case "06" : dateTime = "Jun "
      Case "07" : dateTime = "Jul "
      Case "08" : dateTime = "Aug "
      Case "09" : dateTime = "Sep "
      Case "10" : dateTime = "Oct "
      Case "11" : dateTime = "Nov "
      Case "12" : dateTime = "Dec "
      End Select
      dateTime = dateTime + Right(datePart, 2) + ", " + Left(datePart, 4)
    End If

The date should be in YYYYMMDD format (8 characters). If it is, then start building our big date/time string with the month abbreviation (taking the MM part of the date and converting it using a Select statement), followed by the two digit day of the month and the four digit year. Note that we leave the leading zero if the day of the month is two digits because the Cdat function (which converts a string date into a LotusScript date/time value) can correctly interpret the day of the month with a leading zero.

    If Len(timePart) = 6 Then
      If Len(dateTime) <> 0 Then dateTime = dateTime + " "
      dateTime = dateTime + Left(timePart, 2) + ":" + Mid(timePart, 3, 2)
      dateTime = dateTime + ":" + Right(timePart, 2)
    End If

Next, we convert the time and add it to the end of our long date/time string. If the date part was populated above, then we want a space between the date part and the time part. That's what the first If Len... statement does. Then the next two statements simply convert a time in HHMMSS format into one in HH:MM:SS format. It just puts colons between the hours and minutes and between the minutes and seconds.

    ConvertUniversalDateTime = Cdat(dateTime)
    Exit Function
    Error Err, Error$ & Chr$(10) & " in procedure " & Getthreadinfo(1) & ", line " & Cstr(Erl)
End Function

Finally, we take the long date/time string we built and convert it to a LotusScript date/time value and return that value. The end of the function is our standard error trapping, as described in our Debugging Practices document.

Where would you use this function? Well, like I said earlier, I use it when dealing with DXL. All dates (as far as I've seen) returned from DXL are in the text string. I convert those to the LotusScript date/time format for further processing.