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 是系统表
2、格式化日期:
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