Wednesday, June 8, 2011

how to convert TIMESTAMP format to different date format

It is very often that we need to get specific format of date from TIMESTAMP column. We can certainly get timestamp as string and do some string operation on it to compose a new string with desired formated. However, it will be just nice to be able to get what I want directly from SQL query output. Here is a brief conclusion on how to get formated Date string from TIMESTAMP.

Basically, two DB2 functions are used. DATE scalar function and CHAR scalar function. In CHAR function, we can specify five different format: iso, usa, eur, jis, local.

Below is sample output for different format,

SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), eur) 
  FROM SYSIBM.SYSDUMMY1; 
Output EUR format: 
2011-05-28 15:36:12.178455 28.05.2011

SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), usa) 
  FROM SYSIBM.SYSDUMMY1; 
Output USA format: 
2011-05-28 15:38:57.487165 05/28/2011

SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), jis) 
  FROM SYSIBM.SYSDUMMY1; 
Output JIS format: 
2011-05-28 15:40:00.455681 2011-05-28

SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), iso) 
  FROM SYSIBM.SYSDUMMY1; 
Output ISO format: 
2011-05-28 15:41:12.159374 2011-05-28


SELECT CURRENT_TIMESTAMP, 
char(date (CURRENT_TIMESTAMP), local)
  FROM SYSIBM.SYSDUMMY1; 
Output LOCAL format: 
2011-05-28 15:42:41.802835 05/28/11

No comments:

Post a Comment