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

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
    ACCEPTED ANSWER

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

    ‏2013-01-12T09:40:14Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

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

      ‏2013-01-13T09:28:02Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

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

        ‏2013-01-14T07:56:14Z  in response to SystemAdmin
        Hi Avanish,

        I'm glad that your issue has been solved.

        Good lcuk