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