Topic
  • 3 replies
  • Latest Post - ‏2013-01-14T07:56:14Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic String for TIME using variables H (0 to 23) & M (0 to 59)

‏2013-01-12T09:26:09Z |
Working on project to convert a Microsoft Access database application to DB2 10.1. This my first with DB2.

1st I tried SQL Script
SQL Script:
INSERT INTO "IBNserv"."ChkMe"("Clt")>>> [Column Clt Data Type TIME NOT NULL
DEFALUT 00:00:00]
VALUES CAST('12:14:00' AS TIME)
STATUS
INSERT INTO "IBNserv"."ChkMe"("Clt") VALUES CAST('12.14.00' AS TIME)

Updated 1 rows.

Query execution time => 47 ms
2nd I tried SP

CREATE PROCEDURE SP_Time ()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE H SMALLINT;DECLARE M SMALLINT;DECLARE S SMALLINT;
SET H=12;SET M=14;SET S=00;
INSERT INTO "IBNserv"."ChkMe"("Clt")
VALUES CAST('H:M:S' AS TIME);
END P1
DB2ADMIN.SP_TIME - Deploy for debug started.
DB2ADMIN.SP_TIME - Create stored procedure completed.
DB2ADMIN.SP_TIME - Deploy for debug successful.
3rd I tried to Run SP & following code is the result.
{CALL DB2ADMIN.SP_TIME(?)}
The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.63.108
Run of routine failed.
- Roll back completed successfully.

What is the correct string representation of a TIME value? I want to use variable H (0 to 23) &
Variable M (0 to 59) .Variable S will always be 0.
Updated on 2013-01-14T07:56:14Z at 2013-01-14T07:56:14Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: String for TIME using variables H (0 to 23) & M (0 to 59)

    ‏2013-01-12T09:40:14Z  
    Hi Avanish,

    Welcome to DB2 world :-)

    Please note that the string 'H:M:S' doesn't contain any numbers. It's the value being passed to the cast. It doesn't reference the variable you declared earlier.
    You may define a varchar variable (i.e. T) to hold the concatenation of your three variables in a valid time format and pass it to the cast function. Something like this:

    DECLARE T varchar(8);
    ....
    set T = H || ':' || M || ':' || S; -- May be you can replace (':' || S) with (':00') is S is always zeros.
    .... CASET(T as TIME);
    ....

    Also, this procedure doesn't return any result set, you may drop that clause.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: String for TIME using variables H (0 to 23) & M (0 to 59)

    ‏2013-01-13T09:28:02Z  
    Hi Avanish,

    Welcome to DB2 world :-)

    Please note that the string 'H:M:S' doesn't contain any numbers. It's the value being passed to the cast. It doesn't reference the variable you declared earlier.
    You may define a varchar variable (i.e. T) to hold the concatenation of your three variables in a valid time format and pass it to the cast function. Something like this:

    DECLARE T varchar(8);
    ....
    set T = H || ':' || M || ':' || S; -- May be you can replace (':' || S) with (':00') is S is always zeros.
    .... CASET(T as TIME);
    ....

    Also, this procedure doesn't return any result set, you may drop that clause.
    Dear AnasMosaad the same line was suggested by philipl1jb on IT Knowledge Exchange
    I am reproducing my thanks & final SP with result set as follows
    Thanks a lot philpl1jb! After 5 days constant tries, you have clinched the issue by showing the way out
    Thanks again & again .This is SP & with result sets. Thanks apcalogic
    CREATE PROCEDURE SP_Time (IN H SMALLINT,M SMALLINT, OUT T TIME)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE HC CHAR(2);DECLARE MC CHAR(2);DECLARE TC CHAR(8);
    SET HC=CASE WHEN H BETWEEN 0 AND 9 THEN CAST('0'||H AS CHAR(2))
    WHEN H BETWEEN 10 AND 23 THEN CAST(H AS CHAR(2))
    ELSE CAST('00' AS CHAR(2)) END;
    SET MC=CASE WHEN M BETWEEN 0 AND 9 THEN CAST('0'||M AS CHAR(2))
    WHEN M BETWEEN 10 AND 59 THEN CAST(M AS CHAR(2))
    ELSE CAST('00' AS CHAR(2)) END;
    SET TC= HC||':'||MC||':'||'00';
    SET T= CAST(TC AS TIME);
    END P1
    Status:
    Run: DB2ADMIN.SP_TIME(SMALLINT, SMALLINT, TIME)
    {CALL DB2ADMIN.SP_TIME(?,?,?)}
    Run of routine completed successfully.
    Query execution time => 78 ms
    Parameters VALUE VALUE
    H >>INPUT>>SMALLINT>>12--------1
    M>>INPUT>>SMALLINT>>14-------15
    T>>OUTPUT>>TIME>>>>>>>>>>12:14:00-----01:15:00
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: String for TIME using variables H (0 to 23) & M (0 to 59)

    ‏2013-01-14T07:56:14Z  
    Dear AnasMosaad the same line was suggested by philipl1jb on IT Knowledge Exchange
    I am reproducing my thanks & final SP with result set as follows
    Thanks a lot philpl1jb! After 5 days constant tries, you have clinched the issue by showing the way out
    Thanks again & again .This is SP & with result sets. Thanks apcalogic
    CREATE PROCEDURE SP_Time (IN H SMALLINT,M SMALLINT, OUT T TIME)
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE HC CHAR(2);DECLARE MC CHAR(2);DECLARE TC CHAR(8);
    SET HC=CASE WHEN H BETWEEN 0 AND 9 THEN CAST('0'||H AS CHAR(2))
    WHEN H BETWEEN 10 AND 23 THEN CAST(H AS CHAR(2))
    ELSE CAST('00' AS CHAR(2)) END;
    SET MC=CASE WHEN M BETWEEN 0 AND 9 THEN CAST('0'||M AS CHAR(2))
    WHEN M BETWEEN 10 AND 59 THEN CAST(M AS CHAR(2))
    ELSE CAST('00' AS CHAR(2)) END;
    SET TC= HC||':'||MC||':'||'00';
    SET T= CAST(TC AS TIME);
    END P1
    Status:
    Run: DB2ADMIN.SP_TIME(SMALLINT, SMALLINT, TIME)
    {CALL DB2ADMIN.SP_TIME(?,?,?)}
    Run of routine completed successfully.
    Query execution time => 78 ms
    Parameters VALUE VALUE
    H >>INPUT>>SMALLINT>>12--------1
    M>>INPUT>>SMALLINT>>14-------15
    T>>OUTPUT>>TIME>>>>>>>>>>12:14:00-----01:15:00
    Hi Avanish,

    I'm glad that your issue has been solved.

    Good lcuk