DBDATE

The DBDATE environment variable specifies the end-user formats of DATE values. On UNIX systems that use the C shell, set DBDATE with this syntax.

                   .-MD-.  .-Y4-.  .-/--.
>>-setenv--DBDATE--+-DM-+--+-Y2-+--+- --+----------------------><
                   +-Y4-+  +-MD-+  +-.--+
                   '-Y2-'  '-DM-'  +-.--+
                                   '-0--'

The following formatting symbols are valid in the DBDATE setting:

- . /

are characters that can appear as separators in a date format.

0

indicates that no separator is displayed between time units.

D, M

are characters that represent the day and the month.

Y2, Y4

are characters that represent the year and the precision of the year.

DBDATE can specify the following attributes of the display format:

• The order of time units (the month, day, and year) in a date
• Whether the year appears with two digits (Y2) or four digits (Y4)
• The separator between the month, day, and year time units

For the U.S. English locale, the default for DBDATE is MDY4/, where M represents the month, D represents the day, Y4 represents a four-digit year, and slash ( / ) is the time-units separator (for example, 01/08/2002). Other valid characters for the separator are a hyphen ( - ), a period ( . ), or a zero (0). To indicate no separator, use the zero. The slash ( / ) is used by default if you attempt to specify a character other than a hyphen, period, or zero as a separator, or if you do not include any separator in the DBDATE specification.

If DBDATE is not set on the client, any DBDATE setting on the database server overrides the MDY4/ default on the client. If DBDATE is set on the client, that value (rather than the setting on the database server) is used by the client.

The following table shows some examples for setting DBDATE.

Formats Y4MD* (because asterisk is not a valid separator) and MDY4 (with no separator defined) both display the default symbol (slash) as the separator.

Also, certain routines that IBM Informix ESQL/C calls can use the DBTIME variable, rather than DBDATE, to set DATETIME formats to international specifications. For more information, see the discussion of the DBTIME environment variable in DBTIME and in the IBM Informix: ESQL/C Programmer's Manual.

End-user formats affect the following contexts:

• When you display DATE values, IBM Informix products use the DBDATE environment variable to format the output.
• During data entry of DATE values, IBM Informix products use the DBDATE environment variable to interpret the input.

For example, if you specify a literal DATE value in an INSERT statement, the database server expects this literal value to be compatible with the format that DBDATE specifies. Similarly, the database server interprets the date that you specify as the argument to the DATE( ) function to be in DBDATE format.

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 Informix database servers 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).