More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
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 Table 1 for a list of the date formats that are supported by DB2. 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.
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 |
Table 1: DB2 DATE formats.
(Continued on next question...)