Converting long values from legacy systems to date fields

• MIKE SCHMITZ
• Published: 17 Sep 2002

Often times legacy systems that we connect to store dates as an 8-digit, integer value in for format yyyymmdd, for example 20020704 would be July 4, 2002. I have tried numerous versions of the Format function, including General Date and m/d/yyyy but always end up with a goofy date, such as 12/31/9999. Here is an approach to convert these 8-digit values into a Notes Date field.

In this example, the field INVDT in olddoc is a number field and contains the 8-digit value read from a legacy system. InvoiceDate in newdoc is a date/time field.

invdt& = Clng(olddoc.INVDT(0)) ' first get the value into a variable of type long, in my example the value is 20020704 tempmo% = invdt& Mod 10000 ' store the least significant 4 digits, in my example the value is 704 tempval& = invdt& - tempmo% ' now remove the least significant 4 digits from initial value, in my example this is 20020000 Yr% = tempval& \ 10000 ' now remove the trailing 0's from tempval, result is 2002 Da% = tempmo% Mod 100 ' now get the least significant 2 digits from tempmo, in my example the value is 4 Mo% = (tempmo% - Da%) \ 100 ' And now remove these least significant digits to get a month, in my example, 7 newdoc.InvoiceDate = Datenumber(Yr%, Mo%, Da%) ' Now I can call Datenumber with 2002, 7, 4 to get a date value.