Convert number to Time of Day SQL-CodePudding

The time of day in our system is stored as a number, how do you convert 83315 to 8:33:15 AM ?

CodePudding user response：

Here is one way (assuming you need the output in string data type):

with
  inputs (tod_number) as (
    select  83315 from dual union all
    select 143305 from dual
  )
select tod_number,
       to_char(to_date(to_char(tod_number, 'fm00g00g00'
                              , 'nls_numeric_characters=.:')
                      , 'hh24:mi:ss')
              , 'hh:mi:ss AM') as tod_string
from   inputs
;

TOD_NUMBER   TOD_STRING 
----------   -----------
     83315   08:33:15 AM
    143305   02:33:05 PM

The with clause is not part of the actual query - it's there just to simulate input data. Remove it, and replace the table name (inputs in my query) and column name (tod_number) with your actual table and column names. Change the column name for the output (tod_string) as needed.

The solution begins by converting the number to a string in the hh24:mi:ss format (pretending that the group separator is : so that the string has colons instead of commas or periods). Then convert this to a date, with the incomplete format model hh24:mi:ss; Oracle adds a date component, but you don't care what that is, either on input or on output. Then convert back to a string with the desired format model, hh:mi:ss AM.