When connecting to a Time Matters SQL database using the HotDocs Database Connection, you may need to convert a Time Matters date value (which is stored as a plain number in the database) to a HotDocs Date variable.

Because Time Matters stores its dates as plain numbers in the underlying database table, the HotDocs Database Connection will let you link only those database fields to Number variables.

In the following example, say the underlying database record contains two "dates", birth_date and contract_date. Additionally, assume the Database Connection has been used to link these two database fields to the HotDocs Number variables, birth_date_num and contract_date_num.

The first step in converting these values is to create a temporary Number variable named TempTMDate. (When creating the variable, click the Advanced tab at the Number Variable Editor and clear Ask automatically, Warn when unanswered, and Save in answer file.)

The next step is to create a Computation variable named TMDateConvert. It should contain the following script:

IF TempTMDate < 32000
      28 DEC 1800 + TempTMDate DAYS
ELSE IF TempTMDate < 64000
       8 AUG 1888 + (TempTMDate - 32000) DAYS
ELSE IF TempTMDate < 96000
       20 MAR 1976 + (TempTMDate - 64000) DAYS
ELSE IF TempTMDate < 128000
       30 OCT 2063 + (TempTMDate - 96000) DAYS
END IF

To explain, this script takes into consideration two relevant facts:

1. Time Matters stores dates as an integer representing the number of days since 28 DEC 1800 ; and

2. The DATE + NUM DAYS Expression model currently supports only numbers of days less than 32,000. This means that you must include the ELSE instructions to take into consideration numbers of days greater than 32,000. (Otherwise, you could simply use the script 28 DEC 1800 + TempTMDate DAYS.) This script (in its entirety) handles dates from 19 MAY 1713 to 10 JUN 2151.

So now if you want to merge these dates into a document, you could use the following script in the text of the template:

«SET TempTMDate TO birth_date_num»«TMDateConvert» and
«SET TempTMDate TO contract_date_num»«TMDateConvert»

You could also set actual Date variables to the correct values using the following script:

SET TempTMDate TO birth_date_num
SET SomeDateVar1 TO TMDateConvert
SET TempTMDate TO contract_date_num
SET SomeDateVar2 TO TMDateConvert