Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    6

    Question Unanswered: How to assign Date values into variable?

    Hi,

    How to assign date values into variable in DB2 proc. please find my sample proc below,

    CREATE PROCEDURE P_CLR_TEST ()
    BEGIN
    DECLARE varRunDate DATE;
    SET varRunDate = CURRENT DATE ;

    Insert into tab1(eff_Date) Values (varRunDate);
    END

    if i run this proc i got the below error,
    THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF || IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=4.13.111

    i have tried below options also, but still i get this error.

    select CURRENT DATE INTO varRunDate
    FROM SYSIBM.SYSDUMMY1;

    varRunDate = (select CURRENT DATE FROM SYSIBM.SYSDUMMY1);

    i'm creating and running the proc using DataStudio3.1.

    Please help to solve this issue.

    Thanks in Advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The error you posted does not match the code you posted. Please post the correct code.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    CREATE PROCEDURE P_CLR_TEST ()
    BEGIN
    DECLARE varRunDate CHAR(10);
    SET varRunDate = CURRENT DATE ;
    Insert into tab1(eff_Date) Values (varRunDate);
    END


    Within an application program, a host variable for dates is CHAR(10). It is CHAR(26) for timestamp. This does not apply if the date or timestamp column is completely within an SQL statement such as SELECT, INSERT, UPDATE, DELETE, but if put into a host variable it assumes the external format.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    May 2013
    Posts
    6
    That is a huge proc thats y i hv placed sample proc...

    And thanks for your input...i will try to change the proc and test.

Posting Permissions

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