Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Adelaide, South Australia
    Posts
    3

    Unanswered: Stored Procedure Date format inconsistant

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2003
    Location
    Adelaide, South Australia
    Posts
    3
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2003
    Location
    Adelaide, South Australia
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •