Need A Solution for This

donraja_ht · #1 (**permalink**) 04-22-09, 03:34

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

stolze · #2 (**permalink**) 04-22-09, 04:32

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.

donraja_ht · #3 (**permalink**) 04-22-09, 04:57

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.

tonkuma · #4 (**permalink**) 04-22-09, 06:10

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech