MavidDeyers MavidDeyers - 1 year ago 145
SQL Question

Oracle SQL: Converting a flexible integer into time

i can convert a 4-digit-number ('2300' e.g.) into ('23:00') with the following Statement:

select to_char ( to_date ( '2300', 'HH24MI'), 'HH:MI') integer_time from dual;

Result:
INTEGER_TIME
--------
11:00


But how can I help myself if the values in the database are in addition stored as three digit value, if the first value is below then 10 (e.g. '937' for 9:37).

I kinda Need a double check Statement for this, anyone got a clue?

Answer Source

You need to LPAD it with '0' first

SELECT LPAD('937', 4, '0') FROM dual

A call to LPAD(.., 4) with a 4 character string is a no-op

SELECT LPAD('1937', 4, '0') FROM dual
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download