DATE Considerations using Informix XPS Load Stages

Informix uses the DBDATE or GL_DATE environment variables for interpreting and displaying the string representation of the DATE data type.

For example, if DBDATE is set to DMY4, then the dd-mm-yyyy string is interpreted as dd day of mm month of yyyy year.

Reading DBDATE or GL_DATE environment variables automatically for date formatting by Informix XPS Load is not an option, since you can run IBM® InfoSphere® DataStage® jobs on a physical machine different from the one where the Informix XPS server is installed.

The default DBDATE Informix setting is MDY4. Informix understands the date yyyy-mm-dd correctly only if the DBDATE setting is Y4MD. Otherwise, an error occurs when converting a string to a date. Independent of the DBDATE setting, Informix correctly interprets the following:

  • Date separator as - or /
  • Two-digit as compared to four-digit year (MDY4 is equivalent to MDY2 dates such as mm-dd-yy)

Use the Date Format property to specify the format of date strings that the Informix XPS Load stage sends to Informix. The value of this property should correspond to the value of DBDATE or GLDATE for the target database.