
Ved Renden 31 2870 Dyssegaard Tel. +45 23 34 54 43
| 
MainframeSupports tip week 8/2008:
Sometimes I am surprised by my lacking knowledge about certain parts of DB2. So maybe
there is nothing new to you in this tip. It is about date formats in DB2. Until recently
I beleived that date formats accepted by DB2 was controlled by a parameter in DSNZPARM or
to the precompiler. I already knew that DB2 can handle five different date formats,
namely ISO and JIS which are the same (YYYY-MM-DD), EUR (DD.MM.YYYY), USA (MM/DD/YYYY)
and LOCAL which you define using a DB2 exit. It never occured to me that all those formats
may be used at the same time. Try this example at your installation:
SELECT DATE('9999-12-31')
, DATE('31.12.9999')
, DATE('12/31/9999')
FROM SYSIBM.SYSDUMMY1
WHERE CURRENT DATE > '2008-2-5'
AND CURRENT DATE > '5.2.2008'
AND CURRENT DATE > '2/5/2008'
For the last 18 years I would have been sure that the above SQL statement would fail (if we
ignore the fact that SYSIBM.SYSDUMMY1 has not been around for such a long time). Well, it
does not fail and you do not have to specify any leading zeroes in day or month. Without any success I have
tried to find out when all this was made available in DB2. Maybe date
formats has been working in this manner when dates was introduced in version 1.3.
The SQL statement above returns the same three dates in the same format, which is the format
chosen as default output format at your installation. You can of course also control the
output format for each individual date using the CHAR function or you can control it on
package/DBRM level. Let us view the CHAR function first:
SELECT CHAR(DATE('9999-12-31'), ISO)
, CHAR(DATE('9999-12-31'), EUR)
, CHAR(DATE('9999-12-31'), USA)
, CHAR(DATE('9999-12-31'), LOCAL)
FROM SYSIBM.SYSDUMMY1
Maybe this SQL statement fails at your installation. If it does the DB2 exit for LOCAL
is not activated. Please replace LOCAL with JIS and try again. If LOCAL works it will
probably return a format equal to one of the other formats, but some installations has
defined their own output format. The LOCAL input format does not have to match the LOCAL
output format even though I do not think it is used anywhere.
The output date format for the precompiler is controlled by option DATE(<date-format>),
which ensures that the output format for dates in the corresponding program is equal to
the specified format when the CHAR function is not used. Almost all installations use
some kind of source control system with predefined compilation procedures where options to
the DB2 precompiler has to be specified in some special manner. You have to ask around at
your installation on how to specify the DATE option in your environment.
Previous tip in english
Sidste danske tip
Tip list
|