On Date Formats in DB2 – Part 1

By | In Blog | February 09th, 2016

Regular readers of my blog know that from time to time I use the blog as a forum to answer questions I get via e-mail. Today, we address a popular theme – dealing with DB2 date data…

Q:I have a DATE column in a DB2 table, but I do not want it to display the way DB2 displays it by default. How can I get a date format retrieved from a column in a table from DB2 database in the format MM/DD/YYYY?

A:The simplest way to return a date in the format you desire is to use the built-in column function CHAR. Using this function you can convert a date column into any number of formats. The specific format you request, MM/DD/YYYY, is the USA date format. So, for example, to return the date in the format you requested for a column named START_DATE you would code the function as follows:

CHAR(START_DATE,USA)

The first argument is the column name and the second argument is the format. Consult the following table for a list of the date formats that are supported by DB2.

Name

Layout

Example

ISO

yyyy-mm-dd

2002-10-22

USA

mm/dd/yyyy

10/22/2002

EUR

dd.mm.yyyy

22.10.2002

JIS

yyyy-mm-dd

2002-10-22

LOCAL

Locally defined layout

N/A

You may also have an installation-defined date format that would be named LOCAL. For LOCAL, the date exit for ASCII data is DSNXVDTA, the date exit for EBCDIC is DSNXVDTX, and the date exit for Unicode is DSNXVDTU.

This blog was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2008/10/on-date-formats.html

Craig Mullins
Consultant at Mullins Consulting, Inc
Craig S. Mullins is working with Datavail and its DB2 practice to expand offerings. He is president and principal consultant at Mullins Consulting, Inc. and the publisher of The Database Site. Mullins has 30 years of experience in all facets of database management and is the author of two books: “DB2 Developer’s Guide” currently in its 6th edition and “Database Administration: The Complete Guide to DBA Practices and Procedures,” the industry’s only guide to heterogeneous DBA.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).