在 Oracle 中使用 DateDiff()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16493474/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using DateDiff() in Oracle
提问by Martin
Please consider the following query. I've realised that using fans.checkout-fans.checkinto calculate the difference between two dates (in days) is not a very good idea. How can I rewrite it so that I can use DateDiff()instead? I can't seem to simply replace fans.checkout-fans.checkinwith this function.
请考虑以下查询。我已经意识到使用fans.checkout-fans.checkin来计算两个日期之间的差异（以天为单位）并不是一个好主意。我该如何重写它以便我可以使用它DateDiff()？我似乎不能简单地fans.checkout-fans.checkin用这个函数替换。
select x.name
from (
select fans.name,
dense_rank() over (order by fans.checkout-fans.checkin desc) as rnk
from fans
where fans.checkout-fans.checkin is not null
) x
where x.rnk = 1
回答by Justin Cave
Why do you believe that it is not a good idea to subtract two dates to get the number of days between them? That's certainly the standard way to do that sort of date arithmetic in Oracle.
为什么您认为将两个日期相减以获得它们之间的天数不是一个好主意？这当然是在 Oracle 中进行此类日期算术的标准方法。
DateDiffis not a function that exists in Oracle. I know it exists in SQL Server. You could, of course, write your own function and call that
DateDiff不是 Oracle 中存在的函数。我知道它存在于 SQL Server 中。当然，您可以编写自己的函数并调用它
CREATE OR REPLACE FUNCTION dateDiff( p_dt1 IN DATE,
p_dt2 IN DATE )
RETURN NUMBER
IS
BEGIN
RETURN p_dt1 - p_dt2;
END;
It's not obvious, though, what benefit you derive from doing this rather than simply continuing to subtract the two dates.
但是，这样做比简单地继续减去两个日期有什么好处并不明显。
回答by Asromi rOmi
try this one :
试试这个：
NUMTODSINTERVAL(SYSDATE - @OLD_DT , 'DAY')
OR
TRUNC (SYSDATE) - @OLD_DT
