Summary

A developer asks:

When we expose properties of Type "Date" to table columns, Process Commander chooses column datatype VARCHAR2(#) on Oracle database. 

Because of this, we cannot use direct SQL queries on this column to do date comparisons or sorts. 

We would like to know whether this is a bug.  If not, what is the reason for this?  And what are the recommended ways of using these columns?

Suggested Approach

The issue is that the JDBC drivers and underlying databases have varying and proprietary conventions for when they decide to convert a date or timestamp value from a client time zone to server time zone.  

As a result, if something is defined as a Date column or Timestamp column, Process Commander cannot be certain that the value that is returned from a database query will be the same as the value it placed in that column earlier when it was stored. 

If the database and servers are running in different time zones, such conversions may occur with many possible permutations.

To bypass this issue, use of VARCHAR is recommended for Date property values, and Process Commander saves a Date as 8 digits in the format YYYYMMDD. That format is also sortable and supports comparisons, but you cannot directly use arithmetic on the values, such as subtracting 20061215 from 20070909.

To compute date values (such as the difference between two dates), call one of the many standard function rules in the Date or BusinessDays libraries.