Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Question Unanswered: SQL0440N : Setting Date to Variable

    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.

  2. #2
    Join Date
    May 2003
    Posts
    113

    Re: SQL0440N : Setting Date to Variable

    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?

    Originally posted by ChanOnna
    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.

  3. #3
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    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

  4. #4
    Join Date
    Dec 2003
    Posts
    8
    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

Posting Permissions

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