Hi,

I've got a problem with Stored Procedures returning dates. When returned as a parameter, the date is formatted as ISO '2003-12-31', while if returned in a cursor, the date is formatted as USA '12/31/2003'.

This is a problem as we are in Australia where the date is as in Europe '31/12/2003'.

To reproduce:
CREATE PROCEDURE DB2INST.Proc1 ( OUT P_DATE Date )
LANGUAGE SQL
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
Select current date from SYSIBM.sysdummy1;
Set P_DATE =Current Date;
OPEN cursor1;
END P1

Returns:
DB2INST.PROC1 - The value(s) of the output parameters:
P_DATE = 2004-01-09

DB2INST.PROC1 - Returned the resultset from the stored procedure:
1
----------
01/09/2004
1 record(s) selected.

DB2INST.PROC1 - Returned 0

I have not found any help for this in the manuals or config files. The DB settings are (get db config for lmstest):
Database configuration release level = 0x0900
Database release level = 0x0900

Database territory = AU
Database code page = 819
Database code set = ISO8859-1
Database country code = 61

Every help, much appreciated!

Regards,
Marco Kuipers
South Australia

I am not sure why you selecting current date. You can do a “SET P_DATE = Current Date” without first selecting it from SYSIBM.sysdummy1.

DB2 always stores the date value in the same internal format using 4 bytes, regardless of how it is output in select statement because of a location default or override in a particular SQL statement. So you don’t need to convert it for updates or in a where clause.

If you do want to override the output date format, you can use “CHAR(P_DATE, EUR)” or “CHAR(current date, EUR)” in a select statement. Check out the CHAR function in the SQL Reference Vol 1 for more information.

Thanks Marcus,

I was selecting the current date from sysdummy1 just to give a working example.
I would like to find the location where DB2 decides to return a cursor date in USA format, while the parameter is in ISO.

If I format the date as an ISO and then return as a Char, i've got other problems.

All I want is to find the flag/setting to return cursor dates also as ISO dates.

Regards,
Marco

There may be some confusion here. When a date is returned to an application program, it is always in character format (10 bytes including separators) even though there are only 4 bytes used to store the date internally within DB2. By using the CHAR function, you can change the output format as needed for your location.

So your statement "If I format the date as an ISO and then return as a Char" makes no sense. You can return the date as ISO, USA, EUR, or JIS, either by installation default, or by using the CHAR function to override the default for your installation.

You can input the date in any format and DB2 will normally recognize it correctly, regardless of what the default output format is. Although, I can see that 01-08-2004 and 08-01-2004 are ambiguous. But if you use 2004-01-08, DB2 will know it is January.

The default output format is determined during installation, I believe by using the codepage and location definitions of the database.

Thanks Marcus,

I was not aware that the date is always returned as a char. Thanks for the work-a-round. It's just that I now have to remember everytime to Char(mydate,ISO) all dates in a cursor.
When I forget and test with a day < 13 I do not find the problem during testing.

Still hoping for a config setting though. Expecially as the database is created as En_Au...

Cheers from Oz,
Marco