Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13

    Unanswered: UDF - varchar args too long

    Hi,

    IGNORE ALL THIS - I WAS SILLY & HAD A MISPLACED BRACKET - have updated it below & it's correct now, so I thought I would leave the q here anyway, but this isn't a question any more...


    I have 2 user defined functions (as below) which I want to use to be able to convert from default java format date time to valid db2 date string, which seem like they should be simple enough.

    Code:
    create function GETMON ( mon varchar( 3 ) ) 
    returns varchar( 2 ) 
    return 
    	case mon 
    		when 'Jan' 
    		then '01' 
    		when 'Feb' 
    		then '02' 
    		when 'Mar' 
    		then '03' 
    		when 'Apr' 
    		then '04' 
    		when 'May' 
    		then '05' 
    		when 'Jun' 
    		then '06' 
    		when 'Jul' 
    		then '07' 
    		when 'Aug' 
    		then '08' 
    		when 'Sep' 
    		then '09' 
    		when 'Oct' 
    		then '10' 
    		when 'Nov' 
    		then '11' 
    		when 'Dec' 
    		then '12' 
    		else '00' 
    	end
    Code:
    create function DATEFORMAT( jvstr varchar(50) ) 
    returns varchar( 50 ) 
    return 
    substr( jvstr, 25, 4 ) || '-' || GETMON( substr( jvstr, 5, 3 ) ) || '-' || 
    substr( jvstr, 9, 2 ) || '-' || substr( jvstr, 12, 2 ) || '.' || 
    substr( jvstr, 15, 2 ) || '.00.000000'

    GETMON works completely fine & as expected, but when I run "values DATEFORMAT( 'Thu Jan 01 10:00:00 EST 1970' )", I get the error SQL0433N Value "Jan-1970-10.00" is too long. SQLSTATE=22001

    This seems weird to me, since the input & return strings are well under 50 chars, and according to my understanding, 'Jan-1970-10.00' should not appear anywhere in the return, but I could totally be missing something - I'm only starting with UDF's today...

    I'm stuck - does anyone have any ideas??

    oh and forgot to mention - it's DB2 9.5
    Last edited by gamo; 07-28-09 at 00:52.

Posting Permissions

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