Oracle NUMBER to TIME

Question

我正在写一些ETL来从遗留的Oracle数据库中获取数据并将其放入SQL Server数据库中用于分析服务等

旧oracle数据库将多个时间列存储为Number(4,2)。

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

是否有任何内置函数将其转换为时间数据类型?有人以前遇到过这个吗?你是怎么解决的?

谢谢

Answer 1

Oracle没有TIME数据类型-它有DATE或TIMESTAMP，两者都有日期和时间组件。

SQL小提琴

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;

查询1 -获取数字的正确时间分量:

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' )

结果

:

|  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 |

查询2 -获取今天日期:

的有效时间的数字的正确时间分量

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' )

结果

:

|  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 |

查询3 -获取任何小时(单位部分)和分钟(小数部分)的正确时间分量:

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

结果

:

|  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 |

Answer 2

EDIT2:根据MT0更正查询。这也将照顾场景，当你有:之间，而不是.

        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:它只适用于您给出的值。如果你有一个无效的时间，那么

就会失败

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