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