Time mask HH:MM from a number type field using oracle sql for reporting

Question

I have a query that I use to report hours worked, I tried to use to_char and some other examples I found in stackoverflow.

SELECT CASE WHEN BC.ENTRADA1 IS NULL
        THEN 0
        ELSE REGEXP_REPLACE(lpad(to_char(BC.ENTRADA1),4,'0'), '([0-9]{2})([0-9]{2})', '\1:\2')
        END  AS "Ent1"
FROM BCOEXT BC

Returns the following error:

ORA-00932: inconsistent data types: expected NUMBER got CHAR 00932. 00000 - "inconsistent datatypes: expected %s got %s"

I am using Oraclesql and SQL Developer, I thank anyone who can help

If there’s any other way to make that mask it would be helpful.

Answer 1

If you have a field number, for example 1522 would be "15:22", you can convert the number to string with TO_CHAR and use SUBSTR to pick up the time and minutes digits, like this:

substr(to_char(BC.ENTRADA1),1,2) ||':'|| substr(to_char(BC.ENTRADA1),3,2)

Basically:

• converts to char
• takes the first two digits (subtr(...,1,2))
• concatenate with ":"
• takes the last two digits,(subtr(...,3,2))

See here working: http://sqlfiddle.com