Difference in DB2 Date returned

> The date in the database is being stored in MM/DD/CCYY format, but when my SP is trying to retireve it into a cursor and send it to the front end, the output date is returned in CCYY-MM-DD format.

The date is actually stored as an integer. You can retrieve it in various ways, including the integer value:

SELECT DAYS(CURRENT_DATE) FROM SYSIBM.SYSDUMMY1;

USA format:

SELECT CHAR(CURRENT_DATE, USA) FROM SYSIBM.SYSDUMMY1;

JIS format:

SELECT CHAR(CURRENT_DATE, JIS) FROM SYSIBM.SYSDUMMY1;

and the default format, which may be any of the various CHAR formats:

SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1;

which implies a conversion to CHAR, since you're probably storing it in a String host variable.

> My current DB2 v7.1 was working as desired, now it is has been upgraded to DB2 v8.1. Can it be an issue of DB version?

No. Seems as if the default format was changed from USA to JIS between V7 and V8. Typically during an install you start with the installation parameters from the prior version, to keep things the same as much as possible. But in your case, either that didn't happen or someone chose to change the default format for the DATE to CHAR conversions.

Safest thing to do is to specify the specific format you want in the SELECT statement, so you won't have to worry about the possibility of the default getting changed, which happen at any time, not just during an upgrade.