Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: Date Format and UDF

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I don't know much about iSeries,
    what if SET OPTION statement was placed before RETURN statement in fuction body?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •