Troubleshooting
Problem
The IBM Toolbox for Java, by default, uses the IBM i system job settings for determining formatting of time, date, and timestamp fields. If ISO formats are required, they must be requested by using connection properties.
Resolving The Problem
The IBM Toolbox for Java and JTOpen JDBC driver uses IBM i-specific format options when accessing time and date data types. The date and time format options affect the format of literal values, the format used with the getString() and setString() methods, and the values of the data that can be accessed for date and time fields. The format is controlled by optional JDBC properties that can be specified on the URL.
The default behavior of the toolbox JDBC driver uses the format options defined for the operating system user profile used to make the connection. This default value is different than the native JDBC driver that defaults to ISO format.
Note: Any value other than ISO does not conform with the JDBC specification. JDBC-compliant applications may encounter one of the following errors:
Incorrect String Format
The java.sql.Date field defines string conversions as being locale independent. For example, the specification defines the following conversions:
The toolbox JDBC driver returns string conversions formatted as specified in the operating system job options. Applications not written specifically for IBM i might not expect this format.
Null Values and SQL0183 (MSGSQL0183) - Result of Date or Timestamp Expression Not Valid
If a date value is out of the allowed range for the current job format options, a SQL0183 warning is raised and a null value is returned. Applications that ignore warnings might not report the error.
Resolution
Applications that require ISO formats or JDBC-defined string conversions must set the toolbox connection properties that control the date and time format to ISO. For example, the following URL sets date and time to use ISO formats (the separator properties are not used when ISO format is specified):
For more information, see the toolbox JDBC Properties documentation. This documentation can be found in the IBM Knowledge Center.
Applications can also consider using the JDBC escape sequences to specify literals. For example: "
The default behavior of the toolbox JDBC driver uses the format options defined for the operating system user profile used to make the connection. This default value is different than the native JDBC driver that defaults to ISO format.
Note: Any value other than ISO does not conform with the JDBC specification. JDBC-compliant applications may encounter one of the following errors:
Incorrect String Format
The java.sql.Date field defines string conversions as being locale independent. For example, the specification defines the following conversions:
Date: "toString() - Formats a date in the date escape format yyyy-mm-dd. "
Time: "toString() - Formats this Time object as a String object with the format hh:mm:ss. "
The toolbox JDBC driver returns string conversions formatted as specified in the operating system job options. Applications not written specifically for IBM i might not expect this format.
Null Values and SQL0183 (MSGSQL0183) - Result of Date or Timestamp Expression Not Valid
If a date value is out of the allowed range for the current job format options, a SQL0183 warning is raised and a null value is returned. Applications that ignore warnings might not report the error.
Resolution
Applications that require ISO formats or JDBC-defined string conversions must set the toolbox connection properties that control the date and time format to ISO. For example, the following URL sets date and time to use ISO formats (the separator properties are not used when ISO format is specified):
"jdbc:as400://RCHASSLH;date format=iso;time format=iso;"
For more information, see the toolbox JDBC Properties documentation. This documentation can be found in the IBM Knowledge Center.
Applications can also consider using the JDBC escape sequences to specify literals. For example: "
insert into test (date1, time1) values ({d '0001-01-01'}, {t '12:00:00'})
". The escaped literals work with all format options.
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
24151515
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
Version Independent
Operating system(s):
IBM i
Document number:
640947
Modified date:
27 December 2019
UID
nas8N1017268
Manage My Notification Subscriptions