Abstract
Among other things, the built-in CHAR function provides format conversion between different standards for datetime values. However, with the CHAR function, a timestamp is merely converted to a character string representation.
This user-defined SQL function (CHAR_TS ) extends the conversion feature of the CHAR function to the timestamp for the standards that are appropriate for the format : ISO EUR or JIS.
CHAR_TS works similarly to the CHAR function. It accepts two arguments: a timestamp expression and a literal for the desired conversion.
Content
To create this user-defined function, download ctsudf.txt and upload it to your OS/390® or z/OS® system. Then submit the SQL from SPUFI. You can also run this SQL in the DB2® Command Center or any other SQL processor.
After this user-defined function is created, you can call it as you would any other function. For example, you can use following queries to test the
function:
SELECT CHAR_TS(CURRENT_TIMESTAMP,CHAR('JIS')) FROM SYSIBM.SYSDUMMY1; SELECT CHAR_TS(CURRENT_TIMESTAMP,CHAR('EUR')) FROM SYSIBM.SYSDUMMY1; SELECT CHAR_TS(CURRENT_TIMESTAMP,CHAR('ISO')) FROM SYSIBM.SYSDUMMY1;
These queries return the following output:
---------+---------+---------+---------+---------+---------
SELECT ADMF001.CHAR_TS(CURRENT_TIMESTAMP,CHAR('JIS')) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------
2004-05-27.11:51:21.528031
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
SELECT ADMF001.CHAR_TS(CURRENT_TIMESTAMP,CHAR('EUR')) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------
27.05.2004.11.51.21.540039
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
SELECT ADMF001.CHAR_TS(CURRENT_TIMESTAMP,CHAR('ISO')) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------
2004-05-27.11.51.21.550492
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
Example submitted by: David Seibert, Compuware Corporation
Rate this example
Notice: This example is from the DB2 Examples Trading Post and is provided on an "as-is" basis. You may copy and modify this example in any form without payment to IBM, for the purposes of designing and developing application programs conforming to the application programming interface for the z/OS or OS/390 operating system for which these examples are written. This example has been tested, but it has not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of this example in your particular environment. Use this example as a model for your own situations.
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software GroupTechnical Sales DB2
