Wednesday, December 1, 2004

More fun with dates in LotusScript



By Mick Moignard

I'm sure that anyone who's had more than a passing involvement with Notes applications and with LotusScript has had involvement with date/time fields. Indeed I've written about some aspects of date fields before, see DominoPower Magazine January 2003, "Tips and tricks in LotusScript", http://www.dominopower.com/issues/issue200302/script001.html, where I mentioned how date fields are stored, and how the internal value can be manipulated via a Variant date type.

Dates seem to be an ongoing problem, and indeed in the project I'm still working on we've dealt with a number of date-field issues. The application deals with synchronizing data entered in a Notes-client front-end system with a back-end Oracle database, using LEI (Lotus Enterprise Integrator) and Scripted Activities, which connect to Oracle using the back-end connectors and the Lotus Connector LSX (LotusScript Extension), lsxlc. We've had some interesting incidents with null date values, and we've also had to find ways around other content and representational issues. Let's look at a couple of them.

Getting rid of the time component

The first one has been about trying to lose the time-portion of a Notes date-time value. In Oracle, as in Notes, date type values contain a date and a time. In most cases the front-end application is only interested in dates. While many of the front-end fields truncate the time part, quite a few don't. We experimented quite a bit before we found, what was in hindsight, the rather obvious answer.

First we checked using NotesDateTime.DateOnly, but of course this returns a string, not a date. So that's no good. One reason is because when you try to put a string date value back into a Notes Date/Time field, you're at the mercy of how the date string is interpreted, which I covered in the January 2003 article. It's not good practice to convert strongly typed values like dates to weaker types, like a string, and back again.

Then we tried setting the time portion of the time to zero, and predictably that displayed dates with zero times. Also no good. But we knew that Notes could store a date with no time, because we could see it in the properties box and with Notespeek. We found that a field typed as "date only" appeared as follows in Notespeek.

name    "dateonly"
type    Time
class   Time
flags   Summary
length  8
value   <29/11/2004>    ; 00256F5B:FFFFFFFF

While a field defined as date and time had a value line that looked like this:

value   <29/11/2004 11:03:00>   ; 80256F5B:003CB310

And the difference is obvious. But how to set the time portion to x&quot;FF&quot;?