如何在PLSQL中找到2个日期之间的差异 [英] How to find the Difference Between 2 dates in PLSQL
问题描述
以下是我的查询:
Quote:选择a.Sent,b.Recd,((b .Recd-a.Sent)* 1440)DIFF
来自TableA b,TableB a
其中b.type =''AAA''
和b.ID = a.ID1
其中ID和ID1是外键
两者都发送,Recd日期是格式如''
Quote:5/9/2011 5:22:00 PM
''
但我没有得到:
Quote:((b.Recd-a.Sent)* 1440 )
在几分钟内
相反,我得像
Quote:-000000000 06:48:00
我只需要在几分钟内完成DATE的差异。
请建议。
SELECT
截断(DATE1-DATE2)天,
mod (trunc((DATE1-DATE2)* 24 ), 24 )HOURS,
mod (trunc((DATE1-DATE2)* 1440 ), 60 )MINUTES,
mod (trunc((DATE1-DATE2)* < span class =code-digit> 86400 ), 60 )SECONDS
FROM dual;
根据您的表格更改
查看此链接
http://stackoverflow.com/questions/206222/oracle-best-select-statement-获得两分之间的差异 [ ^ ]
这应该这样做:选择
round((second_date - first_date)* < span class =code-digit> 1440 , 2 )您需要对值进行舍入。
我建议您尝试使用 NUMTODSINTERVAL [< a href =http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions108.htmtarget =_ blanktitle =新窗口> ^ ]功能与第二个''MINUTE''的参数。
NUMTODSINTERVAL(b.recd - a.sent,''MINUTE'')
Below is my Query :
Quote:Select a.Sent, b.Recd, ((b.Recd-a.Sent)*1440) DIFF
from TableA b, TableB a
where b.type = ''AAA''
and b.ID = a.ID1
Where ID and ID1 are foreign Key
Both sent, Recd dates are in format like ''Quote:5/9/2011 5:22:00 PM
''
But i am not getting :Quote:((b.Recd-a.Sent)*1440)
in minutes
Instead i am getting likeQuote:-000000000 06:48:00
I need the difference of DATE in Minutes only.
Please suggest.解决方案SELECT trunc(DATE1-DATE2) days, mod( trunc( ( DATE1-DATE2 ) * 24 ), 24) HOURS, mod( trunc( ( DATE1-DATE2 ) * 1440 ), 60 ) MINUTES, mod( trunc( ( DATE1-DATE2 ) * 86400 ), 60 ) SECONDS FROM dual;
change as per ur table
check out this link
http://stackoverflow.com/questions/206222/oracle-best-select-statement-for-getting-the-difference-in-minutes-between-two[^]
This should do it:select round((second_date - first_date) * 1440,2)You need to round the value.
I suggest that you try using the NUMTODSINTERVAL[^] function with the second parameter of ''MINUTE''.
NUMTODSINTERVAL(b.recd - a.sent, ''MINUTE'')
这篇关于如何在PLSQL中找到2个日期之间的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!