Oracle相当于SQL Server / Sybase DateDiff

We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle.

我们现在使用NHibernate连接到我们软件安装位置的不同数据库。所以我将许多SQL程序移植到Oracle。

SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate.

SQL Server有一个很好的函数叫DateDiff，它接受一个date part，startdate和enddate。

Date parts examples are day, week, month, year, etc. . .

日期部分示例是日，周，月，年等。。

What is the Oracle equivalent?

什么是Oracle等价物？

I have not found one do I have to create my own version of it?

我还没有找到一个我必须创建自己的版本吗？

(update by Mark Harrison) there are several nice answers that explain Oracle date arithmetic. If you need an Oracle datediff() see Einstein's answer. (I need this to keep spme SQL scripts compatible between Sybase and Oracle.) Note that this question applies equally to Sybase.

（由Mark Harrison更新）有几个很好的答案可以解释Oracle日期算术。如果你需要Oracle datediff（），请参阅爱因斯坦的答案。（我需要这样来保持Sybase和Oracle之间的spme SQL脚本兼容。）请注意，此问题同样适用于Sybase。

4 个解决方案

#1

JohnLavoie - you don't need that. DATE in Oracle is actually a date and time data type. The only difference between DATE and TIMESTAMP is that DATE resolves down to the second but TIMESTAMP resolves down to the micro second. Therefore the Ask Tom article is perfectly valid for TIMESTAMP columns as well.

JohnLavoie - 你不需要那个。 Oracle中的DATE实际上是日期和时间数据类型。 DATE和TIMESTAMP之间的唯一区别是DATE可以解析为秒，但TIMESTAMP可以解析为微秒。因此，Ask Tom文章对TIMESTAMP列也完全有效。

#2

I stole most of this from an old tom article a few years ago, fixed some bugs from the article and cleaned it up. The demarcation lines for datediff are calculated differently between oracle and MSSQL so you have to be careful with some examples floating around out there that don't properly account for MSSQL/Sybase style boundaries which do not provide fractional results.

我几年前从一篇旧汤姆文章中偷走了大部分内容，修复了文章中的一些错误并将其清理干净。 daterial的分界线在oracle和MSSQL之间的计算方式不同，所以你必须小心一些漂浮在那里的例子，这些例子没有正确地解释不提供小数结果的MSSQL / Sybase样式边界。

With the following you should be able to use MSSQL syntax and get the same results as MSSQL such as SELECT DATEDIFF(dd,getdate(),DATEADD(dd,5,getdate())) FROM DUAL;

使用以下内容，您应该能够使用MSSQL语法并获得与MSSQL相同的结果，例如SELECT DATEDIFF（dd，getdate（），DATEADD（dd，5，getdate（）））FROM DUAL;

I claim only that it works - not that its effecient or the best way to do it. I'm not an Oracle person :) And you might want to think twice about using my function macros to workaround needing quotes around dd,mm,hh,mi..etc.

我只声称它有效 - 而不是它的有效或最佳方式。我不是甲骨文的人:)你可能要三思而后行使用我的函数宏来解决需要围绕dd，mm，hh，mi..etc引用的问题。

(update by Mark Harrison) added dy function as alias for dd.

（Mark Harrison更新）将dy函数添加为dd的别名。

CREATE OR REPLACE FUNCTION GetDate 
RETURN date IS today date;
BEGIN
RETURN(sysdate);
END;
/

CREATE OR REPLACE FUNCTION mm RETURN VARCHAR2 IS BEGIN RETURN('mm'); END;
/
CREATE OR REPLACE FUNCTION yy RETURN VARCHAR2 IS BEGIN RETURN('yyyy'); END;
/
CREATE OR REPLACE FUNCTION dd RETURN VARCHAR2 IS BEGIN RETURN('dd'); END;
/
CREATE OR REPLACE FUNCTION dy RETURN VARCHAR2 IS BEGIN RETURN('dd'); END;
/
CREATE OR REPLACE FUNCTION hh RETURN VARCHAR2 IS BEGIN RETURN('hh'); END;
/
CREATE OR REPLACE FUNCTION mi RETURN VARCHAR2 IS BEGIN RETURN('mi'); END;
/
CREATE OR REPLACE FUNCTION ss RETURN VARCHAR2 IS BEGIN RETURN('ss'); END;
/

CREATE OR REPLACE Function DateAdd(date_type IN varchar2, offset IN integer, date_in IN date )
RETURN date IS date_returned date;
BEGIN
date_returned := CASE date_type
    WHEN 'mm'   THEN add_months(date_in,TRUNC(offset))
    WHEN 'yyyy' THEN add_months(date_in,TRUNC(offset) * 12)
    WHEN 'dd'   THEN date_in + TRUNC(offset)
    WHEN 'hh'   THEN date_in + (TRUNC(offset) / 24)
    WHEN 'mi'   THEN date_in + (TRUNC(offset) /24/60)
    WHEN 'ss'   THEN date_in + (TRUNC(offset) /24/60/60)
    END;
RETURN(date_returned);
END;
/

CREATE OR REPLACE Function DateDiff( return_type IN varchar2, date_1 IN date, date_2 IN date)
RETURN integer IS number_return integer;
BEGIN
number_return := CASE return_type
    WHEN 'mm'   THEN ROUND(MONTHS_BETWEEN(TRUNC(date_2,'MM'),TRUNC(date_1, 'MM')))
    WHEN 'yyyy' THEN ROUND(MONTHS_BETWEEN(TRUNC(date_2,'YYYY'), TRUNC(date_1, 'YYYY')))/12
    WHEN 'dd'   THEN ROUND((TRUNC(date_2,'DD') - TRUNC(date_1, 'DD')))
    WHEN 'hh'   THEN (TRUNC(date_2,'HH') - TRUNC(date_1,'HH')) * 24
    WHEN 'mi'   THEN (TRUNC(date_2,'MI') - TRUNC(date_1,'MI')) * 24 * 60
    WHEN 'ss'   THEN (date_2 - date_1) * 24 * 60 * 60
    END;
RETURN(number_return);
END;
/

#3

Tom's article is very old. It only discusses the DATE type. If you use TIMESTAMP types then date arithmetic is built into PL/SQL.

汤姆的文章很老了。它只讨论DATE类型。如果使用TIMESTAMP类型，那么日期算法将内置到PL / SQL中。

http://www.akadia.com/services/ora_date_time.html

DECLARE
ts_a timestamp;
ts_b timestamp;
diff interval day to second;
BEGIN
  ts_a := systimestamp;
  ts_b := systimestamp-1/24;
  diff := ts_a - ts_b;
  dbms_output.put_line(diff);
END;
+00 01:00:00.462000

要么

DECLARE
ts_b timestamp;
ts_a timestamp;
date_part interval day to second;

BEGIN
  ts_a := systimestamp;
  date_part := to_dsinterval('0 01:23:45.678');
  ts_b := ts_a + date_part;
  dbms_output.put_line(ts_b);
END;

04-SEP-08 05.00.38.108000 PM

#4

YOU Could write a function in oracle for this

你可以在oracle中为此编写一个函数

function        datediff( p_what in varchar2, p_d1 in date, p_d2 in date) return number as  l_result    number; 
BEGIN
      select (p_d2-p_d1) * 
             decode( upper(p_what), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) 
       into l_result from dual; 

      return l_result; 
END;

and use it like :

并使用它像：

DATEDIFF('YYYY-MM-DD', SYSTIMESTAMP, SYSTIMESTAMP)

#1

#2