Oracle 相当于 SQL Server/Sybase DateDiff
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43819/
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
Oracle equivalent to SQL Server/Sybase DateDiff
提问by David Basarab
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，它接受一个日期部分，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()，请参阅爱因斯坦的回答。（我需要这个来保持 spme SQL 脚本在 Sybase 和 Oracle 之间兼容。）请注意，这个问题同样适用于 Sybase。
回答by andy47
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 列也完全有效。
回答by andy47
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.
我从几年前的一篇旧汤姆文章中窃取了大部分内容，修复了文章中的一些错误并进行了清理。datediff 的分界线在 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.
我只声称它有效 - 而不是它有效或最好的方法。我不是 Oracle 人 :) 而且您可能要三思而后行，使用我的函数宏来解决需要在 dd、mm、hh、mi.. 等周围使用引号的方法。
(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;
/
回答by andy47
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
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
or
或者
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
回答by Kamyar Gilak
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)
