DB2自定義函數source方式創建

DB2自定義函數source方式創建   在coolsql 編輯器創建：  1、簡單的獲取當前時間的day 

create function fmt_dt(timestamp) 
returns varchar(32) source day(timestamp) 
test: select fmt_dt(current datetime) from SYSIBM.sysdummy1 
print: 10 
* SYSIBM.sysdummy1 是系統表 

create function ts_fmt(TS timestamp, fmt varchar(100)) 
returns varchar(100) 
return with tmp (dd,mm,yyyy,hh,mi,ss,ms) as 
( 
    select day(ts),month(ts), 
    year(ts), hour(ts), minute(ts), 
    second (ts), microsecond(ts) 
from SYSIBM.sysdummy1 
) 
select 
     case fmt 
      when 'yyyymmdd' 
          then yyyy||mm||dd 
      when 'mm/dd/yyyy' then mm||'/'||dd||'/'||yyyy 
      when 'yyyy-mm-dd' then yyyy || '-' || mm || '-' || dd 
      when 'yyyy-mm-dd hh:mi:ss.ms' 
           then yyyy || '-' || mm || '-' 
               || dd || ' ' || hh || ':' || mi 
               || ':'|| ss || '.' || ms 
      else 'date format' || coalesce(fmt,'')||' not recognized' 
      end 
from tmp 

test: 

select ts_fmt(current timestamp,'ssyyyymmdd') from SYSIBM.sysdummy1 

print : 
2013-7-10 17:38:18.909000