Body

Here is an example to extract time and date in a certain format from a column declared as timestamp.

C:\Program Files\IBM\SQLLIB_01\BIN>db2level
DB21085I  This instance or install (instance name, where applicable: "DB2_01")
uses "64" bits and DB2 code release "SQL10057" with level identifier
"0608010E".
Informational tokens are "DB2 v10.5.700.375", "s151221", "IP23951", and Fix
Pack "7".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB~1" with DB2 Copy Name
"DB2COPY2".

C:\Program Files\IBM\SQLLIB\BIN>db2 connect to tryme

   Database Connection Information

 Database server        = DB2/NT64 10.5.7
 SQL authorization ID   = DB2ADMIN
 Local database alias   = TRYME

c:\Program Files\IBM\SQLLIB\bnd>db2 create table tryme(col1 int, col2  timestamp)"  
DB20000I  The SQL command completed successfully.  
   
c:\Program Files\IBM\SQLLIB\bnd>db2 "insert into tryme values(1,  current timestamp)"  
DB20000I  The SQL command completed successfully.  
   
c:\Program Files\IBM\SQLLIB\bnd>db2 "select * from tryme"  
   
COL1        COL2  
----------- --------------------------  
          1 2016-09-15-18.42.29.776000   
  1 record(s) selected.  
   
c:\Program Files\IBM\SQLLIB\bnd>db2 "select char (date(col2),usa) from tryme"  
   
1  
----------  
09/15/2016  
   
  1 record(s) selected.  
   
   
c:\Program Files\IBM\SQLLIB\bnd>db2 "select char  (date(col2),usa),char(time(col2),usa) usa from tryme" 
1          USA  
---------- --------  
09/15/2016 06:42 PM  
   
  1 record(s) selected.  
   
You can use a combination of built-in functions to get the desired result; just concatenate the different parts together to get the format  you want.Something like this, assuming the column containing the timestamp is called col2:  
   
In the expression 'char(date(col2), usa)', the date() function obtains  the date portion (year, month, and day) of the timestamp; the char()  function with the 'usa' argument tells DB2 to format the date using the  USA standard, i.e.  2 digit month, 2 digit day, 4 digit year separated  by /.
   
In the expression 'char(time(col2), usa)', the time() function obtains  the time portion of the timestamp; the char() function with the 'usa'  argument tells DB2 to format the time using the US standard, i.e.  hours, followed by a colon, minutes, followed by a space and AM or PM.  
   
The four setting values are as follows:

*Timestamps takes the default format if EUR or USA is specified. The default format is JIS.

Reference:https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0011525.html

Please leave a comment if you have any questions or feedback.

Thank you.

Mary Kassey

IBM DB2 Support