#921 Dates greater than 2040-01-01 shown as null

[Marcelo Nascimento]

On DB2/400, selecting date fields with values greater than 2040-01-01 returns <null> when using JDBC driver.
The functions year(field_name), month(field_name) and day(field_name) returns the inserted value, but the field itself returns <null>.

[Marcelo Nascimento]

Dates greater than 2040-01-01 shown as null

[Beth Auton]

Also, Dates less than 1940 are shown as null
Is there a fix for this?

[Gerd Wagner]

Sorry, I don't have access to DB2/400 and I can't reproduce this problem on other RDBMS's. I'm pretty sure it is an RDBMS/JDBC driver related issue.

[Claus]

Hi,
this may be your solution:
https://www-01.ibm.com/support/docview.wss?uid=nas8N1017268
"jdbc:as400://RCHASSLH;date format=iso;time format=iso;"
Greetings
Claus

[Gerd Wagner]

Thanks, Claus, for your hint. Hope it will be useful.

Thanks to PUB400.COM I now have access to DB2 on AS400. I still couldn't reproduce the problem. I used the JT400 JDBC driver, see http://jt400.sourceforge.net/.

[Gerd Wagner]

I just received a private mail that Claus's hint worked.
So thanks again, Claus.
Gerd

[Claus]

I have full access to a AS400 System i (currently 7r3).
I had tested it using the following SQL:

select date('1939-01-01')
from sysibm.sysdummy1
union all
select date('1940-01-01')
from sysibm.sysdummy1;

First one returns NULL, second one a date:

NULL
1940-01-01

If you change

Global Preferences \ Data type Controls \ Date (SQL type 91)
Use default format (xxxxx) to Long, Medium, Short, ...
Nothing changes.

If you change JDBC-URL to

jdbc:as400://<servername>;date format=iso;time format=iso;

You will get the following result:

1939-01-01
1940-01-01

Only "Bugfix" for SQuirreL could be an Option for a AS400 (Plugin) so you can change this in SQuirreL itself as a option. Otherwise - close the Ticket as "It is not a bug its a feature of JDBC-Driver"

[Gerd Wagner]

Closed on request.