Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Need A Solution for This

    Hello,

    can somebody let me know how to do this, I have a variable xxHrs variable with hours and on integer without minutes, and another variable XXmin what i would like to do is to convert the above two fields into hh:mm , How to do this and is there a data type which can store such time format.


    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since this is not a TIME value, you will have to use a string. So you can convert the integers to strings and simply concatenate them.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2007
    Posts
    56
    Here is what you mean right or wrong.

    SET xxannual_hrs = xxHrs_annualized || '.' ||xxdifMin_annualized;
    SET xxavailable_hrs = xxHrs_available || '.' || xxMin_available;
    SET xxengaged_hrs = xxHrs_engaged || '.' || xxMin_engaged;
    SET xxrealized_hrs = xxHrs_realized || '.' || xxMin_realized;

    where xxannual_hrs, xxavailable_hrs,xxengaged_hrs,xxrealized_hrs are varchar(10), as you say I concatenate then like the above and cast then to time. have i understood it correctly, if not then let me know if there are any changes in it.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT xxHrs  AS "xxHrs"
         , XXmin  AS "XXmin"
         , TIME('00:00:00') + xxHrs HOURS + XXmin MINUTES                     AS "TIME data type"
         , SUBSTR(CHAR(TIME('00:00:00') + xxHrs HOURS + XXmin MINUTES), 1, 5) AS "hh:mm(exp1)"
         , SUBSTR(DIGITS(xxHrs), 9) || ':' || SUBSTR(DIGITS(XXmin), 9)        AS "hh:mm(exp2)"
         , INSERT(SUBSTR(DIGITS(xxHrs*100 + XXmin), 7), 3, 0, ':')            AS "hh:mm(exp3)"
      FROM (VALUES ( 0,  0)
                 , ( 0, 31)
                 , ( 3,  7)
                 , (23, 59)  ) t(xxHrs, XXmin)
    ;
    ------------------------------------------------------------------------------
    
    xxHrs       XXmin       TIME data type hh:mm(exp1) hh:mm(exp2) hh:mm(exp3)
    ----------- ----------- -------------- ----------- ----------- -----------
              0           0 00:00:00       00:00       00:00       00:00      
              0          31 00:31:00       00:31       00:31       00:31      
              3           7 03:07:00       03:07       03:07       03:07      
             23          59 23:59:00       23:59       23:59       23:59      
    
      4 record(s) selected.
    Last edited by tonkuma; 04-22-09 at 14:31.

Posting Permissions

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