DATA CONVERSION FUNCTION ERROR 1

[Ann28]

The date is defined as CHAR (20041231)

select MEMBER_SSN  ,  date (  LEFT ( END_DATE,4) || '-' ||  SUBSTR(END_DATE, 5,2)  || '-'  ||  RIGHT (END_DATE,2 )   )
END_DATE, BENEFIT_PLAN from elg_summary  fetch first 10 rows only

& the error...[red]SQL0440N No authorized routine named "DATE" of type "FUNCTION having
compatible arguments was found. [/red]


Really appreciate your help!!!
Thanks,
Ann

 

[ddiamond]

Ann,

I don't know why your sql doesn't work, but I was able to get your sql to work by replacing the left and right functions with substr.

select MEMBER_SSN  ,  date (  SUBSTR ( END_DATE,1,4) || '-' ||  SUBSTR(END_DATE, 5,2)  || '-'  ||  SUBSTR (END_DATE,7,2 )   )
END_DATE, BENEFIT_PLAN from elg_summary  fetch first 10 rows only

 

[Ann28]

THANKS A LOT !

 

[ddiamond]

Ann,

Another way to make it work is to use the char function.

select MEMBER_SSN  ,  date (  char( LEFT ( END_DATE,4) || '-' ||  SUBSTR(END_DATE, 5,2)  || '-'  ||  RIGHT (END_DATE,2 ) ) )
END_DATE, BENEFIT_PLAN from elg_summary  fetch first 10 rows only