Re: Converting Integer to Dates

Ok, I figured out some more error handling...

CREATE FUNCTION ROB/INTTODATE
(INTDATE INT)
RETURNS DATE
LANGUAGE SQL
RETURNS NULL ON NULL INPUT
BEGIN
DECLARE CHARDATE CHAR(8);
DECLARE RETURNDATE DATE;
DECLARE Continue Handler For SqlException
Set RETURNDATE = null;
SET CHARDATE = CAST(INTDATE AS CHAR(8));
SET RETURNDATE = DATE(SUBSTR(CHARDATE, 1, 4) CONCAT '-' CONCAT
SUBSTR(CHARDATE, 5, 2) CONCAT '-' CONCAT
SUBSTR(CHARDATE, 7, 2));
RETURN RETURNDATE;
END

SELECT INTTODATE(CAST (20071032 AS INT)) from sysibm/sysdummy1
....+....1
INTTODATE
-
******** End of data ********


SELECT INTTODATE(CAST (20071001 AS INT)) from sysibm/sysdummy1
....+....1
INTTODATE
2007-10-01
******** End of data ********

Rob Berendt