I am connecting to db2 iseries 7 with date format = iso. If I execute this statement:
Code:
SELECT DATE('1924-01-28') FROM SYSIBM.SYSDUMMY1
I get a date as expected.
I have a UDF:
Code:
CREATE FUNCTION CC_YY_MM_DD(
CC VARCHAR(2),
YY VARCHAR(2),
MM VARCHAR(2),
DD VARCHAR(2))
RETURNS DATE
LANGUAGE SQL
SPECIFIC CYMD002
NO EXTERNAL ACTION
CONTAINS SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
SET OPTION DATFMT=*ISO
BEGIN
DECLARE INVALID_DATE_ERROR CONDITION FOR SQLSTATE '22007';
DECLARE EXIT HANDLER FOR INVALID_DATE_ERROR
RETURN CAST(NULL AS DATE);
RETURN DATE(
RIGHT('00'||TRIM(CC),2)||
RIGHT('00'||TRIM(YY),2)||
'-'||TRIM(MM)||
'-'||TRIM(DD));
END;
If I execute:
Code:
SELECT CC_YY_MM_DD('19','24','01','28') FROM SYSIBM.SYSDUMMY1;
I get a null because the date is older than 1940. Actually in my development db I get a date, in my test db I get a null. There is some difference between these 2 db's that make the UDF not use the ISO format on the test db. Anyone know why this would be?
dBforums
>
Date Format and UDF
02-02-12, 08:42
Linear Mode
