Sometimes I am surprised by my lacking knowledge about certain parts of DB2. So maybe there is nothing new to you in this tip. It is about date formats in DB2. Until recently I beleived that date formats accepted by DB2 was controlled by a parameter in DSNZPARM or to the precompiler. I already knew that DB2 can handle five different date formats, namely ISO and JIS which are the same (YYYY-MM-DD), EUR (DD.MM.YYYY), USA (MM/DD/YYYY) and LOCAL which you define using a DB2 exit. It never occured to me that all those formats may be used at the same time. Try this example at your installation:

SELECT DATE('9999-12-31')
     , DATE('31.12.9999')
     , DATE('12/31/9999')
FROM SYSIBM.SYSDUMMY1
WHERE CURRENT DATE > '2008-2-5'
  AND CURRENT DATE > '5.2.2008'
  AND CURRENT DATE > '2/5/2008'

For the last 18 years I would have been sure that the above SQL statement would fail (if we ignore the fact that SYSIBM.SYSDUMMY1 has not been around for such a long time). Well, it does not fail and you do not have to specify any leading zeroes in day or month. Without any success I have tried to find out when all this was made available in DB2. Maybe date formats has been working in this manner when dates was introduced in version 1.3.

The SQL statement above returns the same three dates in the same format, which is the format chosen as default output format at your installation. You can of course also control the output format for each individual date using the CHAR function or you can control it on package/DBRM level. Let us view the CHAR function first:

SELECT CHAR(DATE('9999-12-31'), ISO)
     , CHAR(DATE('9999-12-31'), EUR)
     , CHAR(DATE('9999-12-31'), USA)
     , CHAR(DATE('9999-12-31'), LOCAL)
FROM SYSIBM.SYSDUMMY1

Maybe this SQL statement fails at your installation. If it does the DB2 exit for LOCAL is not activated. Please replace LOCAL with JIS and try again. If LOCAL works it will probably return a format equal to one of the other formats, but some installations has defined their own output format. The LOCAL input format does not have to match the LOCAL output format even though I do not think it is used anywhere.

The output date format for the precompiler is controlled by option DATE(<date-format>), which ensures that the output format for dates in the corresponding program is equal to the specified format when the CHAR function is not used. Almost all installations use some kind of source control system with predefined compilation procedures where options to the DB2 precompiler has to be specified in some special manner. You have to ask around at your installation on how to specify the DATE option in your environment.