CODEWITHSUNDEEP
oracle-databasetimeoracle-sqldeveloperdatetime-format
MontyPython
MontyPython

Reputation: 2994

How to convert Text Time into Time in Oracle?

Here's the query that's getting me the output as HH:MM:SS in TEXT FORMAT. I want the output in time format so that it can be manipulated and sorted and searched.

Can I get it as a number?

EXTRACT (DAY FROM interval)*10 + EXTRACT (HOUR FROM interval) - 
        CASE
            WHEN EXTRACT(HOUR FROM CAST(date2 AS TIMESTAMP)) > EXTRACT(HOUR FROM CAST(date 1 AS TIMESTAMP)) 
                THEN 
                    14
                ELSE
                    0 
        END || ':' || EXTRACT (MINUTE FROM interval) || ':' ||
   EXTRACT (SECOND FROM interval) CB_END_TV_START

Upvotes: 0

Views: 140

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

I don't follow the manipulation you're doing on your values (with the day*10 and hour maybe -14), but in principal once you have the separate components, rather than concatenating them with : you could add them together, with each one divided by an appropriate value to convert to fractions of a day (so the hour value divided by 24, the minute by 24*60, etc.).

With a interval value picked at random (and without your adjustments):

with t42 as ( select numtodsinterval(147.147, 'HOUR') as interval from dual)
select interval,
    extract (day from interval)
    + (extract (hour from interval) / 24)
    + (extract (minute from interval) / (24*60))
    + (extract (second from interval) / (24*60*60)) as num
from t42;

INTERVAL           NUM
----------- ----------
6 3:8:49.2    6.131125

The NUM value represents partial days; you can covert that back to an interval with:

select numtodsinterval(6.131125, 'DAY') from dual;

NUMTODSINTERVAL(6.131125,'DAY')
-------------------------------
6 3:8:49.2

Or as a string with something like this, adding it to a known fixed date:

select to_char(date '1900-01-01' + 6.131125 - 1, 'D HH24:MI:SS') from dual;

TO_CHAR(DATE'1900-01-01'+6.131125-1,'DHH24:MI:SS')
--------------------------------------------------
6 03:08:49

Upvotes: 2

Related Questions