CODEWITHSUNDEEP
sqloracle-databasetimeto-dateto-char
MavidDeyers
MavidDeyers

Reputation: 290

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?

Upvotes: 0

Views: 2220

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 1

schurik
schurik

Reputation: 7928

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

Upvotes: 2

Caius Jard
Caius Jard

Reputation: 74595

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

Upvotes: 4

Related Questions