September 5, 2005

Date Comparisons

Here's a topic that confuses many people: Variable types, especially where date values are concerned. Online forums field questions about this topic all the time. I'll use SQR for this discussion, although the same issue occurs with PeopleCode and other languages.

SQR originally had two fundamental variable types: numeric (#variable) and string ($variable). It also supports database columns (&column), although these can hold either numeric or string values. Date values were considered strings and were stored in "$" variables. This is still true, but SQR's later versions add a DATE variable type.

Here's the problem--and it affects Oracle more than, for example, MS SQL Server. SQL Server's date values are normally expressed as "2005-01-01", while Oracle normally uses the "01-JAN-2005" format. January 1, 2005 is "less than" February 1, 2005 because January 1 occurs before February 1. With dates in YYYY-MM-DD format, we can compare them easily:

  let $Jan1 = '2005-01-01'
  let $Feb1 = '2005-02-01'
  if $Jan1 < $Feb1    ! This is true

However, the same code will not work for Oracle's DD-MON-YYYY format. "01-JAN-2005" is now greater than "01-FEB-2005", simply because the letter "J" is higher than "F". Similarly, "02-FEB-2005" compares as greater than "01-MAR-2005" because "02" is higher than "01".

We can solve this in several ways. First, we could use a variable declaration in SQR:

begin-setup
  declare-variable
    date $Jan1 
    date $Feb1 
  end-declare
end-setup

begin-procedure Something
  let $Jan1 = '01-JAN-2005'
  let $Feb1 = '01-FEB-2005'
  if $Jan1 < $Feb1    ! This is true

Another way is to use SQR's STRTODATE function, which takes a string and returns a date value:

  let $Jan1 = '01-JAN-2005'
  let $Feb1 = '01-FEB-2005'
  if strtodate($Jan1) < strtodate($Feb1)    ! This is true

Caution: Storing STRTODATE($Jan1) back into variable $Jan1 turns it back into a string that cannot be compared accurately. The variable can be used for comparisons only if it's declared as DATE.

Another common method which is more portable across databases is to use PeopleSoft's delivered SQCs for handling dates. The format used in these SQCs is called DTU format. (I've never heard what "DTU" stands for.) DTU dates are in "YYYY-MM-DD" format, which is readily compared using normal string variables. First convert any literal or variable date into a DTU-format string:

  let $x = '01-JAN-2005'
  do Convert-To-DTU-Date($x, $DTU_Date)
  if $DTU_Date <   ...

Another way to do this is to use SQR's very flexible EDIT function:

  let $x = '01-JAN-2005'
  let $Jan1 = edit($x, 'YYYY-MM-DD')
  if $Jan1 <   ...

SQR often converts variable types (as when a #variable is moved to a $variable, or a &column is moved to a variable). But we can't expect it to understand that a string variable actually contains a date value unless we use a method such as the ones shown above. Use a consistent format for dates and you'll avoid many hard-to-find bugs.

Until next time...