MayFlower develops specialty software products for Lotus Notes & Domino.




Chapter 7 - Working with Dates
Table of Contents


Dates can be the most difficult ASCII or ODBC source field types.

Different source databases export dates to ASCII in all types of formats.

June 28, 1995 could be represented many different ways. Some formats that we have encountered are:
1995/06/28
1995-06-28
19950628
950628
06/28/95
062895

European Style Dates
28/06/95
28-06-95

The last two are European variations which reverse the day and month. In this example, it is easy to tell which digits represent day and which digits represent month, but it is impossible to determine day or month for dates where the day is less than or equal to 12, like 06/07/95 (is this June 7 or July 6?).

How to Convert these to Notes type Date/Time
The easiest way to convert these values is to use a holding field. A holding field "holds" the source date in a temporary variable that is converted using a Notes formula.

To use the example of 19950628, we will convert that to a Notes Time/Date field type by storing it to the variable "LoadDate", which is not the name of the desired Notes field. That name is simply Date.


On the Task Form, enter the field LoadDate in the Field Types section of the form:

Field Information
Field Types
LoadDate:TEXT

Reassemble the Date into a Notes Format

The technique is to reassemble the date into a format that is compatible with the Lotus Notes Time/Date field.

In the Notes Formula section, use the following formula:

Notes Formula to Run After each Note is added or updated

FIELD Date := @TextToTime(
@Middle(LoadDate; 4; 2) + "/" +
@Middle(LoadDate; 6; 2) + "/" +
@Middle(LoadDate; 2; 2));
SELECT @All


Clean Up of Extra Fields

It may make sense to eliminate the field "LoadDate" from the document. To do this, add the following statement at the end of the formula:
Field LoadDate := @Unavailable

This will eliminate the field LoadDate from the document. This is a good housekeeping idea.