Wednesday, December 1, 2004

More fun with dates in LotusScript

Then one of the project's Notes client front-end developers tried using NotesDateTime.SetAnyTime, which does exactly what we wanted. The Designer Help says in describing the NotesDateTime.SetAnyTime function: "Sets the time component to a wildcard value, which means that it matches any time. The date component is unaffected". It's not at all obvious from that description that this is exactly what we wanted.

In seconds, as you can imagine, we had some code going. Here's a fragment, which copies a date-field value from a Lotus Connector LCFieldlist entry to a Notes field, and gets rid of the time as it does it.

Dim tdate As New NotesDateTime(OracleFieldList.DateTimeField(0))
Call tdate.setanytime 'kills off the time component
Call doc.ReplaceItemValue("NotesField", tdate)

The first line creates a NotesDateTime item and initializes it to the Oracle value as read in the LCConnector fieldlist OracleFieldList field entry DateTimeField. The next line then removes the time component. The last line updates the Notes document. The Notes client will then only display the date, recognizing that there's no time component. Problem solved.

You can also use this technique using NotesDateTime.SetAnyDate to remove the date portion of a field value, and leave just the time.

Preserving null dates

Another issue we found was where we were dealing with null dates coming from Oracle. What alerted us was a rash of complaints about date values of "31/12/1899 00:00:00" turning up in the front-end application. This puzzled us for quite some time, until we did some serious digging into what was going on.

First of all, we double-checked that we really did have Nulls in the Oracle date fields, and we did. Then we checked that we got nulls in the Notes date fields we read the Oracle data into, and we did, too. Figure A shows the properties box for this field, showing that it's a date-time field, but with no value.

FIGURE A

The properties box for the Notes date field. (click for larger image)

And here's what Notespeek made of the same field:

name    "date1"
type    Time
class   Time
flags   Summary
length  8
value   <>      ; FFFFFFFF:FFFFFFFF

As you can see, both the time and the date part are set to x'FF', indicating no value in either part of the field. But when we copied that data to another date field, with the Script line:

doc.copydate = doc.date1(0)

The copydate field contains &quot;31/12/1899 00:00:00&quot; -- as shown by this properties box extract:

Field Name: COPYDATE1
Data Type: Time/Date
Data Length: 8 bytes
Seq Num: 1
Dup Item ID: 0
Field Flags: SUMMARY

30/12/1899 00:00:00 GMT

Hmm. That doesn't look right. This LotusScript/Notesbehavior looks to me like a defect; I can't believe that it's supposed to do this.

So we rationalized it down to this: When the data is copied from one field to another, it's copied internally via a Variant date-time value. Such a value is a number, where the date value is to the left of the decimal point, and the time value is to the right. But when this copy happens, the intermediate internal value is seen as zero. And zero becomes a date of 31/12/1899.