Question & Answer
Question
How does the Toolbox JDBC driver deal with dates before 1940 (or after 2039)?
Answer
The IBM i database supports several date formats. The Toolbox JDBC driver uses the date format that is set up as the default on the IBM i system. This default is set by system value QDATFMT which is usually set to YMD, MDY, or DMY. Those date formats only support dates between 1940 and 2039 because they use 2-digit years.
By default, attempts to use dates outside of that range will fail:
C:\Downloads\jtopen_10_7\lib>java -jar jt400.jar jdbc:as400:<MyIBMiName>
>values date('2040-01-01')
00001
null
*** Warning ***
SQLState: 01004
Message: Data truncation
Vendor: 0
*** Warning ***
SQLState: 01534
Message: [SQL0181] Value in date, time, or timestamp string not valid.
Vendor: 181
You can override the date format by specifying the "date format" property when opening the JDBC connection. The best choice is "iso", which supports a full four-digit date. The easiest way to do this is to add ";date format=iso" to the end of the URL used when connecting to the database. For example:
C:\Downloads\jtopen_10_7\lib>java -jar jt400.jar jdbc:as400:<MyIBMiName>;"date format"=iso
>values date('2040-01-01')
00001
2040-01-01
>
Please see the "date format" property under IBM Toolbox for Java JDBC properties
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Product Synonym
IBM i;AS400
Was this topic helpful?
Document Information
Modified date:
24 March 2022
UID
ibm11118001