Sqlstate 22007 Sqlcode -181

[Dobber]

I am trying to construct an SQL statment and pass it to an i5. However I keep getting the above error message. At its simplest I am trying to convert an 8 0 numeric field in the format 20070817 to a date so that I can subtract a number of days. The statement consists of:

Cast((SUBSTR(CHAR(OHDATE),7,2)||'/'|| SUBSTR(CHAR(OHDATE),5,2) ||'/'|| SUBSTR(CHAR(OHDATE),3,2)) As date)

I have also tried:

Date(SUBSTR(CHAR(OHDATE),7,2)||'/'|| SUBSTR(CHAR(OHDATE),5,2) ||'/'|| SUBSTR(CHAR(OHDATE),3,2))

Both these conversions work fine when I paste them into Interactive SQL (strsql) on the i5 i.e

Cast(SUBSTR(CHAR(OHDATE),7,2)||'/'|| SUBSTR(CHAR(OHDATE),5,2)
||'/'|| SUBSTr(CHAR(OHDATE),3,2) As Date ) from orhead
SELECT statement run complete.

CAST function
30/06/07
30/06/07
30/06/07
30/06/07
30/06/07
30/06/07

Has anyone any ideas?

Thanks

[kestrel]

Welcome to The Scripts!
Im going to move this to the SQL forum. Have a nice day!

--
Kestrel