IBM Support

Incorrect setting of the DBDATE environment variable can produce error 1205 - Invalid month in date.

Question & Answer


Question

How to resolve 1205 errors when attempting a select, update or insert on a DATE column.

Cause

The SQL is of one format (eg. DMY4) but the DBDATE environment variable is set to another format (eg. MDY4).

Example:

export DBDATE=DMY4

select * from mytable where date_column = '05/20/1998'

Will cause error 1205.

Answer

Set the DBDATE environment variable to match the format of the SQL being run or change the SQL to match the DBDATE format.

Example (from above):



1. Run "export DBDATE=MDY4" from the command line

or

2. Change the SQL to:

select * from mytable where date_column = '20/05/1998'


IBM Informix products use the following precedence to determine the end-user format for an internal DATE value:
1. DBDATE
2. GL_DATE
3. Information defined in the client locale (if CLIENT_LOCALE is set)
4. Default date format is %m/%d/%iy (if DBDATE and GL_DATE are not set, and no locale is specified)

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"Developer;Enterprise;Express;Growth;Ultimate;Workgroup","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21382114