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!

How does it display when querying the table directly on the server ?

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

If you want to guarantee that the date returned in ISO format (YYYY-MM-DD), then use the following:

select CHAR(date-column, ISO)

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.

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.

And the article stated that we could configure the date-format for database by issuing:
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.

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.

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.