Oracle 12c - Sql Query - Character to Number

SANPAT

Dear Friends

I want to select the data which is in character form (34:44) which i want to convert to number as (34.44).

I tried the following Query but its giving the error message, can you please suggest the correct syntex . clock_in is in Character ("34:44")

SELECT EMP_NO, TO_NUMBER('clock_in', '99.99'), OFF_DUTY,CLOCK_IN,CLOCK_OUT FROM ATT_BIOMATRIX_DRAFT

Sanjay

BEDE

How can you have the hour 34 on a clock?

Still, see if the below suits you:

with t_clock as (

select '22:35' hr_mi

from dual

)

select to_number(replace(hr_mi,':','.')) hr_mi_num

from t_clock

;

Solomon Yakobson

@SANPAT: I want to select the data which is in character form (34:44) which i want to convert to number as (34.44).

It isn't clear what are you clocking. If it is hours and minutes or minutes and seconds then 34:44 is 34 + 44/60 and not 34.44. Using 34.44 will not work if you are, for example adding/subtracting clocks:

with t_clock as (
                 select '34:44' hr_mi from dual union all
                 select '00:16' hr_mi from dual
                )
select  sum(replace(hr_mi,':','.')) s1,
        sum(regexp_substr(hr_mi,'[^:]+') + regexp_substr(hr_mi,'[^:]+',1,2) / 60) s2
  from  t_clock
/

        S1         S2
---------- ----------
      34.6         35

SQL>

SY.

User_H3J7U

Replacing a colon with a dot depends on NLS. For limited number of digits:

to_number('12:34','99d99','nls_numeric_characters=:,')