Setting the Session Date Format in IBM DB2 for i (and AS/400, and iSeries)

Apparently in DB2 for Linux/Unix/Windows, the date format is a system-wide setting, and is not controlled at the session level. I’m not a DB2 LUW expert, so don’t take my word on that!

IBM DB2 for i offers more flexibility.

In DB2 for i, you can easily control the date format for each connection to the database, and you can even change it from one format to a different one between subsequent queries in the same session.

For example, I recently had a question about this SQL query:

SELECT someDateColumn FROM someTable

Which returned “01/12/09” (mm/dd/yy).

But the user wanted to see the date in mm/dd/ccyy format (01/12/2009).

First, it is important to understand that the session that this query ran in is configured for *MDY date format. The century information is still there, you just can’t see it on screen or paper when the date format is set to *MDY.

DB2 for i DATE data type

DB2 for i DATE data types (“L” in DDS) are stored internally as a serial number (“Lillian” date format, if you are interested, is a numeric value representing the number of days since 14 October 1582). Lillian dates are compact, and make date math and sorting easy, but they aren’t very user-friendly, so DB2 automatically converts DATE columns to the session date format at presentation time.

Session Date Format

When you connect to DB2 for i to run a query, one of the parameters that you supply is the DATE FORMAT to use for that SESSION. If you don’t specify a date format, then the system makes a default decision for you based on system values, the settings of the current job, the setting of the database server job that you are attached to, or other mechanisms depending on the connection method.

How you set the date format varies depending on the interface that you are using. For example in a JDBC session, you can control this using the “date format” connection property, plus a little optional help from the “date separator” property. ODBC uses a similar method. Most green-screen commands and utilities like STRSQL and CRTSQLRPGI have a DATFMT and DATESEP parameter.

JDBC connection string example

Connection c = DriverManager.getConnection(
"jdbc:as400://myAS400;date format=USA;errors=full",
"auser",
"apassword");

Notice the “date format=USA” parameter.

CRTSQLRPGI command example

CRTSQLRPGI ... DATFMT(*ISO)

The following date formats are supported:

yy/dd (*JUL)
mm/dd/yy (*MDY)
dd/mm/yy (*DMY)
yy/mm/dd (*YMD)
mm/dd/yyyy (*USA)
yyyy-mm-dd (*ISO)
dd.mm.yyyy (*EUR)
yyyy-mm-dd (*JIS)

Documentation for your particular database interface (JDBC, ODBC, OLDEB, ADO.NET, STRSQL, embedded SQL in RPG/COBOL/C) will show you how to set the date format and separator.

If you are using the green-screen STRSQL command, you can prompt the STRSQL command and specify the DATFMT and DATSEP parameters, or once you have started the STRSQL utility, you can use F13, Option 1 to change the session parameters. Interactive help is available.

Date functions

DB2 on i offers some very nice date functions for extracting the various components out of date, time, and timestamp fields without having to resort to string parsing (which is a bad coding practice anyway, since a change in date format can break code that uses substring techniques to parse dates).

Instead, use the EXTRACT() function:

EXTRACT(YEAR from MYDATE)

or the individual YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), or MICROSECOND() functions.

And on the subject of date formats, the CHAR() function can convert a date field back to character format, and even allows you to specify the date format on the fly in your query without changing the session date format. If you don’t specify it, you get the session format. For example, this will display the date in USA format (mm/dd/ccyy), even though the session date format is set to *ISO (ccyy-mm-dd):

SELECT char(date(‘2009-01-12’),USA) FROM sysibm/sysdummy1

The DATE() function converts the literal ‘2009-01-12’ to a DATE type, and the CHAR() function converts it back to a character literal, but this time forces it into USA format (mm/dd/ccyy).

The DB2 for i SQL Programmer’s Guide and Reference topics in the IBM Information Center have all of the details on date formats, functions, and just about anything else you need to know about IBM i SQL:

The IBM i Access for Windows Programmer’s Toolbox (an optionally installable component of IBM i Access) contains documentation and examples of how to set and change the session date formats for ODBC, OLE DB, and ADO.NET connections, plus links to documentation for JDBC.

If you need additional assistance, please Contact Us.

Posted in DB2 on i, DB2 on i ADO.NET, DB2 on i DRDA, DB2 on i Embedded SQL, DB2 on i JDBC, DB2 on I ODBC, DB2 on i OLEDB, IBM i, IBM i Access For Windows