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
dBforums
>
UDF - varchar args too long
07-27-09, 23:29
Linear Mode
