Technical Blog Post
Abstract
How to extract time and data in a certain fomat from a DB2 timestamp column?
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:
Format | Date | Time | Timestamp |
JIS | yyyy-mm-dd | hh:mm:ss | yyyy-mm-dd hh:mm:ss.ffffffffffff |
ISO | yyyy-mm-dd | hh.mm.ss | yyyy-mm-dd-hh.mm.ss.ffffffffffff |
EUR | dd.mm.yyyy | hh.mm.ss | yyyy-mm-dd hh:mm:ss.ffffffffffff* |
USA | mm/dd/yyyy | hh:mm AM or PM | yyyy-mm-dd hh:mm:ss.ffffffffffff* |
*Timestamps takes the default format if EUR or USA is specified. The default format is JIS.
Please leave a comment if you have any questions or feedback.
Thank you.
Mary Kassey
IBM DB2 Support
UID
ibm11140598