回复 1# 一诺万世 我测试了一下完全可以啊。应该是DB2版本的问题吧-rw-r--r-- 1 db2inst1 db2iadm1 832 Jan 14 04:46 test.sql-rw-r--r-- 1 db2inst1 db2iadm1 8118 Jan 7 05:01 tmp.sqld...
显示全部回复 1# 一诺万世 我测试了一下完全可以啊。应该是DB2版本的问题吧
-rw-r--r-- 1 db2inst1 db2iadm1 832 Jan 14 04:46 test.sql
-rw-r--r-- 1 db2inst1 db2iadm1 8118 Jan 7 05:01 tmp.sql
db2inst1@testing1:~> db2 -td@ -vf test.sql
CREATE FUNCTION "DB2INST1"."DATE_FORMAT"(TS timestamp, fmt varchar(20))
returns varchar(50)
return
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as
(
select
substr( digits (day(TS)),9),
substr( digits (month(TS)),9) ,
rtrim(char(year(TS))) ,
substr( digits (hour(TS)),9),
substr( digits (minute(TS)),9),
substr( digits (second(TS)),9),
rtrim(char(microsecond(TS)))
from sysibm.sysdummy1
)
select
case fmt
when 'yyyymmdd'
then yyyy || mm || dd
when 'mm/dd/yyyy'
then mm || '/' || dd || '/' || yyyy
when 'yyyy/dd/mm hh:mi:ss'
then yyyy || '/' || mm || '/' || dd || ' ' ||
hh || ':' || mi || ':' || ss
when 'yyyyMMddHH24miss'
then yyyy || mm || dd || hh || mi || ss
when 'yyyyMMdd'
then yyyy || mm || dd
when 'HH24miss'
then hh || mi || ss
when 'nnnnnn'
then nnnnnn
else
'date format ' || coalesce(fmt,' ') ||
' not recognized.'
end
from tmp
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 "values date_format(current timestamp,'ttttmmdd')"
1
--------------------------------------------------
date format ttttmmdd not recognized.
1 record(s) selected.
db2inst1@testing1:~> db2 "values date_format(current timestamp,'yyyymmdd')"
1
--------------------------------------------------
20150114
1 record(s) selected.
db2inst1@testing1:~>
收起