Dealing with null End Dates

(adapted from an article on my UNIFACE page)

Introduction

Converting from database format to user format

Converting from user format to database format

Comments

Introduction

Sometimes an end date can have a null (empty) value which signifies an unspecified date in the future, similar to infinity. This presents a problem when comparing with non-null dates as it would treat the end date as being less than the target date instead of being greater. This is because null is always less than not null.

One way around this is to include the test for a null/empty value in the comparison, as follows:-

SELECT ... WHERE (end_date >= 'target_date' or end_date='' or end_date IS NULL or end_date='0000-00-00')

This tends to get rather cumbersome as this combined test has to be included everywhere. Leave it out just once and see the confusing results!

What we need is a way of testing the date with a simple comparison, like so:

SELECT ... WHERE (end_date >= 'target_date')

This can be done by holding unspecified end-dates on the database as the highest date available (such as '9999-12-31'), but converting them to null before being displayed to the user. This removes the possibility of making a mistake with the multi-part comparison. This requires code to convert the value from infinity to null before displaying the value to the user, and converting back to infinity before writing to the database.

In the following examples I turn on this feature by specifying the 'infinityisnull' option in my $fieldspec array for the database table, and all the formatting/unformatting code is executed automatically. Within the RADICORE framework this facility can be activated by going to the Update Column function in the Data Dictionary and setting the INFINITY IS NULL option for that column before the <tablename>.dict.inc file is (re)built by the export function.

1. Converting from database format to user format

   // look for fields where 'type' = 'date'
   if ($fieldspec['type'] == 'date') {
      if (isset($fieldspec['infinityisnull']) and $fieldvalue == '9999-12-31') {
         // this date is shown to the user as empty
         $fieldarray[$fieldname] = '';
      } else {
         // convert date from internal to external format
         $fieldarray[$fieldname] = $dateobj->getExternalDate($fieldvalue);
      } // if
   } // if

2. Converting from user format to database format

   if ($fieldspec['type'] == 'date') {
      if (strlen($fieldvalue) == 0) {
         if (isset($fieldspec['infinityisnull'])) {
            $fieldvalue = '9999-12-31';
         } else {
            $fieldvalue = null;
         } // if  
      } else {
         // value must be a valid date
         if (!$internaldate = $dateobj->getInternalDate($fieldvalue)) {
            $this->errors[$fieldname] = "$fieldname: " .$dateobj->getErrors();
         } else {
            // set date to internal format
            $fieldvalue = $internaldate;
         } // if
      } // if
   } // if