Hi,

I'm having some trouble converting an integer to a date and setting it to a variable...

Here is the code:

...

DECLARE D_FROMSTARTDATE DATE;

SET D_FROMSTARTDATE = DATE (SUBSTR(CHAR(P_FROMDATE),5,2) || '/' || SUBSTR(CHAR(P_FROMDATE),7,2) || '/' || SUBSTR(CHAR(P_FROMDATE),1,4) );

...

P_FROMDATE is a parameter, coming in as an INTEGER.

The error message when trying to deploy the stored procedure is:

SQL0440N No Authorized routine named "SUBSTR" of type "FUNCTION" having compatible arguements was found.


Thanks for any advice.

It looks ok.
Is it possible that P_FROMDATE is a smallint, which cause
SUBSTR(CHAR(P_FROMDATA),7,2) out of range?

Would you please give a result of
CHAR(P_FROMDATA), and LENGTH(CHAR(P_FROMDATA)) inside your stored proc?

BTW, which db2 you are using? db2 for LUW(linux/unix/windows) for db2 for 390?

If your value is comming as integer , DATE function automaticaly convert into date, no need to convert to char & substring it.


example is as below

db2 select date(1967) from sysibm.sysdummy1

1
----------
05/21/0006

1 record(s) selected.


Let me know if any questions .


Thank You


Lekharaju Ennam
Certified Oracle8i & DB2 UDB DBA
Florida A&M University

Thank you both for your replies.

After seeing your answers, I felt a little more confident with the syntax, and did a little more testing. I actually had a similar date conversion at the end of the script where I left out the CHAR after the SUBSTR.

After adding CHAR to the line, it deployed successfully.

Thanks again for your input.

Best,
Chanonna