- 论坛徽章:
- 0
|
DROP FUNCTION DB2ADMIN.TSToChar;
CREATE FUNCTION DB2ADMIN.TSToChar(v_TS TimeStamp,v_Type varchar(50))
RETURNS VARCHAR(50)
RETURN
WITH Temp (dd,mm,yyyy,hh,mi,ss,nnnnnn) AS
(
SELECT
SUBSTR( digits (DAY(v_TS)),9),
SUBSTR( digits (MONTH(v_TS)),9),
RTRIM(CHAR(YEAR(v_TS))),
SUBSTR( digits (HOUR(v_TS)),9),
SUBSTR( digits (MINUTE(v_TS)),9),
SUBSTR( digits (SECOND(v_TS)),9),
RTRIM(CHAR(MICROSECOND(v_TS)))
FROM sysibm.sysdummy1
)
SELECT
CASE v_Type
WHEN 'yyyymmdd'
THEN yyyy || mm || dd
WHEN 'yyyy/mm/dd'
THEN yyyy || '/' || mm || '/' || dd
WHEN 'yyyy-mm-dd'
THEN yyyy || '-' || mm || '-' || dd
WHEN 'yyyy/dd/mm hh:mi:ss'
THEN yyyy || '/' || mm || '/' || dd || ' ' || hh || ':' || mi || ':' || ss
WHEN 'yyyy-dd-mm-hh-mi-ss'
THEN yyyy || '-' || mm || '-' || dd || '-' || hh || '-' || mi || '-' || ss
ELSE
'错误!输入的转换类型 ' || COALESCE(v_Type,' <null> ') ||
' 不能被识别。'
END
FROM Temp;
/**************************************************************************************************
验证该函数的功能:
db2 values db2admin.TSToChar(CURRENT TIMESTAMP,'yyyy/dd/mm hh:mi:ss')
返回值为: 2006/11/01 11:19:16
db2 values db2admin.TSToChar(CURRENT TIMESTAMP,'yyyy-dd-mm-hh-mi-ss')
返回值为: 2007-01-19-15-36-36
**************************************************************************************************/ |
|