DATE expressions in database objects

When an expression in a database object (including a check constraint, fragmentation expression, SPL routine, trigger, or UDR) contains a literal date value, the database server evaluates the expression using the setting that DBDATE (or other relevant environment variables) had when the database object was created (or was last modified). If DBDATE has been reset to a new value, the new value is ignored when the literal DATE is evaluated.

For example, suppose DBDATE is set to MDY2/ and a user creates a table with the following check constraint on the column orderdate:

orderdate < '06/25/98'

The date of the preceding expression is formatted according to the value of DBDATE when the constraint is defined. The check constraint expression is interpreted as orderdate < '06/25/98' regardless of the value of DBDATE during inserts or updates on the orderdate column. Suppose DBDATE is reset to DMY2/ when a user inserts the value '30/01/98' into the orderdate column. The date value inserted uses the date format DMY2/, whereas the check constraint expression uses the date format MDY2/.

See Abbreviated years and expressions in database objects for a discussion of legacy objects from earlier versions of HCL Informix® that are always evaluated according to the runtime environment. That section describes how to redefine objects so that dates are interpreted according to environment variable settings that were in effect when the object was defined (or when the object was last modified).

Important: The behavior of DBDATE for HCL Informix is not compatible with earlier versions.