Oracle SQL: Converting a flexible integer into time

Question

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?

`'2300'` and `'937'` are strings, not numbers; what data type are you actually working with? — Alex Poole, Jul 27 '17 at 09:09
Sorry, mistake on my part, Integer is right, i corrected it. — MavidDeyers, Jul 27 '17 at 11:43

score 4 · Accepted Answer · answered Jul 27 '17 at 08:38

4

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

answered Jul 27 '17 at 08:38

Caius Jard

72,509
5
49
80

score 2 · Answer 2 · answered Jul 27 '17 at 08:50

2

You can use TO_CHAR with the format 'fm0999' if the value is stored as a NUMBER

select to_date(to_char(937, 'fm0999'), 'HH24MI') from dual;

see format models

answered Jul 27 '17 at 08:50

schurik

7,798
2
23
29

score 1 · Answer 3 · answered Jul 27 '17 at 09:06

As an alternative, you could avoid bouncing through a nominal date and just use number and string manipulation:

select to_char(trunc(937/100), 'FM00') ||':'|| to_char(mod(937, 100), 'FM00') from dual;

TO_CHAR
-------
09:37

... though if your starting value is actually a string rather than a number there's another level of implicit conversion going on. (If it is a string you probably have bigger problems with validation, but the lpad route might be the simplest way if you assume the data is always as expected).

With a range of values:

with t (n) as (
  select 0 from dual
  union all select 1 from dual
  union all select 59 from dual
  union all select 937 from dual
  union all select 2300 from dual
  union all select 2359 from dual
)
select n, to_char(trunc(n/100), 'FM00') ||':'|| to_char(mod(n, 100), 'FM00') 
  as integer_time
from t;

         N INTEGER
---------- -------
         0 00:00  
         1 00:01  
        59 00:59  
       937 09:37  
      2300 23:00  
      2359 23:59

If you don't want the leading zero - i.e. 937 instead of 09:37 - then change the first format model to FM90.

If your data isn't constrained to be integers in the range 0-2359 then this would format 'bad' times, possibly with hashes for the hour part, while bouncing through a date would error. Neither is ideal, but hopefully is a moot point.

Oracle SQL: Converting a flexible integer into time

3 Answers3