CREATE FUNCTION to_char ( t1 TIMESTAMP, format VARCHAR(32) )
RETURNS VARCHAR(26)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN
DECLARE chs_tmstmp CHAR( 26 );
DECLARE retval VARCHAR( 26 );
SET chs_tmstmp = CHAR( t1 );
CASE TRIM(format)
WHEN 'DD'
THEN SET RETVAL = SUBSTR( chs_tmstmp, 9, 2);
WHEN 'MM'
THEN SET RETVAL = SUBSTR( chs_tmstmp, 6, 2);
WHEN 'YYYY'
THEN SET RETVAL = SUBSTR( chs_tmstmp, 1, 4);
WHEN 'YYYY-MM-DD'
THEN SET RETVAL = SUBSTR( chs_tmstmp, 1, 10);
ELSE SIGNAL SQLSTATE '38Z01'
SET MESSAGE_TEXT = 'INVALID FORMAT SPECIFIED.';
END CASE;
RETURN retval;
END;
给你一段网上看到的函数,比较精炼
DROP FUNCTION FUNC_DATETIME_FMT;
CREATE FUNCTION FUNC_DATETIME_FMT(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 'yyyymmddhhmiss'
THEN YYYY || MM || DD || HH || MI || SS
WHEN 'mm/dd/yyyy'
THEN MM || '/' || DD || '/' || YYYY
WHEN 'yyyy/dd/mm hh:mi:ss'
THEN YYYY || '/' || MM || '/' || DD || ' ' ||
HH || ':' || MI || ':' || SS
WHEN 'nnnnnn'
THEN nnnnnn
ELSE
'DATE FORMAT ' || COALESCE(FMT,' ') ||
' NOT RECOGNIZED.'
END
FROM TMP