Community Pick: Many members of our community have endorsed this article.

Setting the Session Date Format in DB2 UDB for AS/400 / iSeries / System i

Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT
30+ year IT expert: IBM i/iSeries/AS400, AIX, Windows, Linux, EDI, security, performance, web, mobile, cloud, SQL, RPG, Java, .NET, more.
Published:
Updated:
November 2009

Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm not a UDB LUW expert, so don't take my word on that!

DB2 UDB for AS/400 is different!

DB2/400 is just a little bit different (and superior in this one particular case, though that is certainly not always the case!).

The good news is that in DB2/400, you can easily control the date format for any given connection to the database, and you can even change it from one format to a different one between subsequent queries in the same session.

The original question showed the output from this query:

SELECT date('01/12/2009')
                        FROM sysibm/sysdummy1                

Open in new window


Returns: 01/12/09, but the user wanted to see the date in mm/dd/ccyy format (01/01/2009).

First of all, understand that the SQL date() function is not dropping the century in the example above.  The session that this query ran in is just configured for *MDY date format.  It really has nothing to do with the DATE() function itself.

The century is still there, you just can't see it on screen or paper when the date format is set to *MDY, as it is apparently in the example query.

Bear with me and I'll explain what I mean.

DB2/400 DATE data type

The SQL DATE() function converts an expression representing a date (a string in this case) into an actual DATE data type field.  

DB2/400 DATE data types are stored internally as a serial number (Lillian date format, if you are interested), so DB2 or the database driver (ODBC/JDBC/etc) as convenience to us humans, converts it automatically into the session date format at presentation time.  

Since the internal serial number is kind of hard to decipher, this is a very handy presentation feature!

The DBMS does this kind of presentation management for us all the time with numeric fields, so dates are really nothing special.  Who wants to see a zoned numeric field or integer displayed in raw format?

Session Date Format

When you connect to DB2/400 to run a query, one of the parameters that you give DB2 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");

Open in new window

*Notice the date format=USA portion!

CRTSQLRPGI command example:  

CRTSQLRPGI ... DATFMT(*ISO)

Open in new window


As of V5R4, 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)

In any case, the documentation for your particular database interface and/or language of choice 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.

There is no danger in doing this, since it only impacts the one specific session that you change it in, and you can even change around to different data formats for different queries within the same job.

Date functions


DB2/400 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 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.  If you want to get tricky and waste a little time, you can change the original query to this and you'll get the result in USA format (mm/dd/ccyy):

SELECT char(date('01/12/2009'),USA) FROM sysibm/sysdummy1   

Open in new window



The DATE() function converts the literal '01/01/2009' to a date-type field, and the CHAR() function converts it back to a character literal, but this time forces it into USA format (mm/dd/ccyy), even though the session date format is MDY (mm/dd/yy).

The DB2/400 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 AS/400 SQL:

http://publib.boulder.ibm.com/iseries/

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

- Gary Patterson

Check out my EE profile:  https://www.experts-exchange.com/M_4382324.html
3
13,616 Views
Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT
30+ year IT expert: IBM i/iSeries/AS400, AIX, Windows, Linux, EDI, security, performance, web, mobile, cloud, SQL, RPG, Java, .NET, more.

Comments (2)

Hello. i need your help, the case is the next
i need to change the format date in a connection JDBC
when i try ti insert a new record with the dateFormat dd-MM-YYYY
i receive the exception oracle.jbo.domain.DataCreationException: JBO-25009: No se puede crear un objeto del tipo: java.sql.Date desde el tipo: java.lang.String con el valor: 13-03-2018

Because the format date in the database is MM-DD-YYYY
i could changue the format date from java but, the i need change it from JDBC connection
Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT

Author

Commented:
Hi Christian,

Unfortunately this article is about IBM i / iSeries / AS/400 DB2 - not Oracle - and I am not an Oracle expert.  

Suggest you "Ask  A Question":

https://www.experts-exchange.com/askQuestion.jsp

Regards,

- Gary

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.