在Oracle中使用DateDiff() [英] Using DateDiff() in Oracle

问题描述

请考虑以下查询.我已经意识到，使用fans.checkout-fans.checkin计算两个日期(以天为单位)之间的差不是一个好主意.如何重写它，以便可以使用DateDiff()代替?我似乎无法简单地用此函数替换fans.checkout-fans.checkin.

Please consider the following query. I've realised that using fans.checkout-fans.checkin to 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.checkin with this function.

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

推荐答案

为什么您认为减去两个日期以获取它们之间的天数不是一个好主意?当然，这是在Oracle中执行这种日期算术的标准方法.

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.

DateDiff不是Oracle中存在的功能.我知道它存在于SQL Server中.您当然可以编写自己的函数并调用该函数

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

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.

这篇关于在Oracle中使用DateDiff()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！