CODEWITHSUNDEEP
oracle-databaseplsql
Matt
Matt

Reputation: 399

Oracle NUMBER to TIME

Im writing some ETL to get data out of a legacy Oracle db and into SQL Server db for Analysis services etc.

The legacy oracle database is storing several time columns as Number(4,2).

9 = 09:00
1.2 = 01:20
11.53 = 11:53

Are there any built in functions that will convert this to a Time data type? Has anyone come across this before? How did you solve it?

Thanks

Upvotes: 2

Views: 9292

Answers (2)

Utsav
Utsav

Reputation: 8093

EDIT2: Corrected the query as per MT0. This will also take care of scenario when you have : in between instead of .

        with tbl as(
        select 9 as tf from dual union all
        select 1.2 from dual union all
        select 11.53 as tf from dual 
        )
        select TO_DATE( TO_CHAR( replace(to_char(tf),':','.'), '00.00' ), 'HH24.MI' ) AS time from tbl

EDIT1: It will only work for the values you gave. If you have something which is not a valid time, then it will fail saying 

 ORA-01850: hour must be between 0 and 23
 or
 ORA-01851: minutes must be between 0 and 59

Upvotes: 0

MT0
MT0

Reputation: 167972

Oracle does not have a TIME datatype - it has DATE or TIMESTAMP both of which have a date and a time component.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE times ( time ) AS
          SELECT  9    FROM DUAL
UNION ALL SELECT  1.2  FROM DUAL
UNION ALL SELECT 15.53 FROM DUAL
UNION ALL SELECT 24.62 FROM DUAL;

Query 1 - Get the correct time component for numbers which are valid times:

SELECT time,
       TO_DATE( TO_CHAR( time, '00.00' ), 'HH24.MI' ) AS parsed_time
FROM   times
WHERE  REGEXP_LIKE( TO_CHAR( time, '00.00' ), '([01]\d|2[0-3]).[0-5]\d' )

Results:

|  TIME |                 PARSED_TIME |
|-------|-----------------------------|
|     9 | September, 01 2015 09:00:00 |
|   1.2 | September, 01 2015 01:20:00 |
| 15.53 | September, 01 2015 15:53:00 |

Query 2 - Get the correct time component for numbers which are valid times with today's date: 

SELECT time,
       TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) || ' ' || TO_CHAR( time, '00.00' ), 'YYYY-MM-DD HH24.MI' ) AS parsed_time
FROM   times
WHERE  REGEXP_LIKE( TO_CHAR( time, '00.00' ), '([01]\d|2[0-3]).[0-5]\d' )

Results:

|  TIME |                 PARSED_TIME |
|-------|-----------------------------|
|     9 | September, 28 2015 09:00:00 |
|   1.2 | September, 28 2015 01:20:00 |
| 15.53 | September, 28 2015 15:53:00 |

Query 3 - Get the correct time component for any number of hours (unit part) and minutes (decimal part):

SELECT time,
       TRUNC( SYSDATE ) + TRUNC(time)/24 + (time - TRUNC(time)) * 100 / 60/ 24 AS parsed_time
FROM   times

Results:

|  TIME |                 PARSED_TIME |
|-------|-----------------------------|
|     9 | September, 28 2015 09:00:00 |
|   1.2 | September, 28 2015 01:20:00 |
| 15.53 | September, 28 2015 15:53:00 |
| 24.62 | September, 29 2015 01:02:00 |

Upvotes: 1

Related Questions