Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: Retrieve Wrong Date 2010-21-01 on Client Side?

    Hi,

    Server: AIX/DB2 9.1
    Client: WinXp Db2 Client 9.1

    The date stored in database is 2010-01-21.
    But when client side retrieve the date through a stored procedure, it returns 2010-21-01! However, it returns the crorrect date to client if client side select the underlying table directly NOT through a sp.
    The stored procedure is very simple. Cant think of anything that could cause this strange bahavior.

    This is only happened to some of the client machines.
    Could anyone help? Thanks!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    How does it display when querying the table directly on the server ?

    The date output format has to do with locale settings ..

    Quote Originally Posted by wilsonfv View Post
    Hi,

    Server: AIX/DB2 9.1
    Client: WinXp Db2 Client 9.1

    The date stored in database is 2010-01-21.
    But when client side retrieve the date through a stored procedure, it returns 2010-21-01! However, it returns the crorrect date to client if client side select the underlying table directly NOT through a sp.
    The stored procedure is very simple. Cant think of anything that could cause this strange bahavior.

    This is only happened to some of the client machines.
    Could anyone help? Thanks!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want to guarantee that the date returned in ISO format (YYYY-MM-DD), then use the following:

    select CHAR(date-column, ISO)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    Quote Originally Posted by sathyaram_s View Post
    How does it display when querying the table directly on the server ?

    The date output format has to do with locale settings ..
    When select the table directly on the SERVER by db2cmd, it returns 01/21/2010 (mm/dd/yyyy).
    But on some of the workstations, it returns 2010-21-01 to IBM WebSphere Server if queries through a sp! Confused. -__-!

    Is it any way to configure the client, like a setting for IBM JDBC? you know it might be difficult to configure the server.

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    Quote Originally Posted by Marcus_A View Post
    If you want to guarantee that the date returned in ISO format (YYYY-MM-DD), then use the following:

    select CHAR(date-column, ISO)
    Yes. That's what we try to do temporarily.
    Before the sp returns the date to IBM WebSpere Server, we CHAR(date-column, ISO).

    But that's not ultimate way, is it?
    Each time when writting a sp, you need to CHAR(date-column, ISO).
    But even if you forget to do so, you will not notice, cause only some of the workstations get a 2010-21-01 from the DB.

    I have googled, then found an article.

    DB2 Basics: Fun with Dates and Times

    In my case, I believe the date-format in database is IBM USA.

    Code:
    International Standards Organization
    ISO
    yyyy-mm-dd
    1991-10-27
    
    IBM USA standard
    USA
    mm/dd/yyyy
    10/27/1991
    
    IBM European standard
    EUR
    dd.mm.yyyy
    27.10.1991
    
    Japanese Industrial Standard Christian Era
    JIS
    yyyy-mm-dd
    1991-10-27
    And the article stated that we could configure the date-format for database by issuing:
    Code:
    On Windows: c:\program files\IBM\sqllib\bnd
    On UNIX:/home/db2inst1/sqllib/bnd
    
    db2 connect to DBNAME 
    db2 bind @db2ubind.lst datetime ISO blocking all grant public
    Is there any drawback with the command?

    I mean if I change the date-format from mm/dd/yyyy to yyyy-mm-dd, dont know if it will cause any un-expected behavior.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The client territory code will still determine the format of the date that is returned to the client, unless it is returned by a program like a stored procedure. That is why you get different results from different workstations hitting the same 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

  7. #7
    Join Date
    Apr 2009
    Posts
    42
    Quote Originally Posted by Marcus_A View Post
    The client territory code will still determine the format of the date that is returned to the client, unless it is returned by a program like a stored procedure. That is why you get different results from different workstations hitting the same database.
    Thanks!

    The client territory code will determine the date format, however when the client select the table directly, it is OK but not OK when through a sp.

    Once I tried to CHAR(date-column) with no ISO format,
    WebSpere Server returned below exception,
    com.ibm.db2.jcc.b.SqlException: Invalid data conversion: Parameter instance 29/04/2005 is invalid for requested conversion to java.sql.Date.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by wilsonfv View Post
    Thanks!

    The client territory code will determine the date format, however when the client select the table directly, it is OK but not OK when through a sp.

    Once I tried to CHAR(date-column) with no ISO format,
    WebSpere Server returned below exception,
    com.ibm.db2.jcc.b.SqlException: Invalid data conversion: Parameter instance 29/04/2005 is invalid for requested conversion to java.sql.Date.
    Yes, I mentioned that. When you go through an application program that runs on the database server, the territory code of the database comes into play. The database server has a territory code, and each client has a territory code.

    However, when you are inserting data with a VALUES clause or using dates in a WHERE clause, if you use the ISO format, it will always work correctly regardless of the territory code without you having to specify the format. Do it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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