在预言机SQL中减去时间

Question

我试图根据情况相互减去时间 但是错误是我得到的是非数字字符在预期数字字符的位置找到

这是代码

select 
case 
when to_date('01-JAN-2019 05:00 AM', 'HH:MI:SS AM') < 
to_date('01-JAN-2019 05:00 PM', 'HH:MI:SS AM') 
then round((to_date('01-JAN-2019 05:00 AM', 'HH:MI:SS AM') - 
to_date('01-JAN-2019 09:00 AM', 'HH:MI:SS AM'))*24,2)
else 0  
end late
from dual

Answer 1

一种选择是使用 ISO 8601 标准时间戳格式为

select case 
when timestamp'2019-01-01 05:00:00' < timestamp'2019-01-01 17:00:00'
then 
timestamp'2019-01-01 05:00:00' - timestamp'2019-01-01 09:00:00'
else interval '0' second
end as late
from dual

如果只需要小时的数值，请考虑：

select case 
when timestamp'2019-01-01 05:00:00' < timestamp'2019-01-01 17:00:00'
then 
extract( hour from timestamp'2019-01-01 05:00:00' 
- timestamp'2019-01-01 09:00:00' )
else 0
end as late
from dual

演示

Answer 2

您必须匹配日期格式和格式掩码：

SQL> select
2  round(
3        (to_date('01-JAN-2019 05:00 AM', 'dd-mon-yyyy HH:MI AM') -
4         to_date('01-JAN-2019 09:00 AM', 'dd-mon-yyyy HH:MI AM')
5        ) * 24, 2) result
6  from dual;
RESULT
----------
-4
SQL>

Answer 3

第一个转换格式和实际字符串格式必须匹配。接下来，在减去之前将日期转换为时间戳，因此结果是间隔。最后，从间隔中提取所需的部分

select 
extract( day from diff ) Days, 
extract( hour from diff ) Hours, 
extract( minute from diff ) Minutes 
from (
select  CAST(to_date('01-JAN-2019 09:00 AM', 'DD-MON-YYYY HH:MI AM') as timestamp) 
- CAST(to_date('01-JAN-2019 05:00 AM', 'DD-MON-YYYY HH:MI AM') as timestamp) diff   
from dual
)