Just recently I've been doing more and more work with SQL and Domino. Sometimes trying to get them to live in unity. This is what I am trying to do now as I work on a Domino With Google Gears proof of concept.

While working with both a SQL backend and a Domino backend one recurring issue I'm finding is that of date-time formats and getting one to understand the other.

If you create a SQL table to hold details of a set of Notes document and you want to add a "Created Date" column then you'll probably get stuck converting Notes' dates to SQL dates.

LotusScript Function

The easiest way I've found is to get them both to talk to each other in "Unix time" -- that is, the number of seconds elapsed since midnight on 1st Jan 1970.

To do this I created a little LotusScript function to work out the Unix time for a given variant of type Date, such as a document's LastModified property. Here's the function:

Function getTimeStamp(dt As Variant) As Long
        Dim dtEpoch As New NotesDateTime("1/1/1970 00:00:00")
        Dim dtTemp As New NotesDateTime(Now)
        dtTemp.LSLocalTime = dt
        getTimeStamp = dtTemp.TimeDifference(dtEpoch)
End Function

Usage:

Msgbox getTimeStamp(doc.LastModified)
Msgbox getTimeStamp(Now)

Prints out something like:

1243500324
1243510655

These values can then be stored in a SQL column of type "timestamp" and we can use SQL to convert to any format we like. Hey presto.

Your Thoughts?

Before I wrote the function I only did a cursory check that there isn't a method to get this number from a NotesDateTime already. Hopefully there isn't and I've not publically shamed myself yet again?