SQL oracle 11g SQL中如何计算两个日期之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25620592/
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
How to calculate difference between two dates in oracle 11g SQL
提问by Santhosh Kancherla
When I am trying to calculate the date difference by using datediff function it showing that invalid identifier.
当我尝试使用 datediff 函数计算日期差异时，它会显示该无效标识符。
SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate from da_static_trade.
Error : invalid identifier.
Can you please tell me what is the function to calculate date difference.
你能告诉我计算日期差异的函数是什么吗？
回答by Mikhail Timofeev
There is no DATEDIFF()function in Oracle. On Oracle, it is an arithmetic issue
DATEDIFF()Oracle 中没有函数。在 Oracle 上，这是一个算术问题
select DATE1-DATE2 from table
回答by Nadeem_MK
Oracle support Mathematical Subtract -operator on Data datatype. You may directly put in select clause following statement:
Oracle 支持-数据数据类型的数学减法运算符。您可以直接在 select 子句中放入以下语句：
to_char (s.last_upd – s.created, ‘999999D99′)
Check the EXAMPLEfor more visibility.
检查示例以获得更多可见性。
In case you need the output in termes of hours, then the below might help;
如果您需要以小时为单位的输出，那么以下内容可能会有所帮助；
Select to_number(substr(numtodsinterval([END_TIME]-[START_TIME]),'day',2,9))*24 +
to_number(substr(numtodsinterval([END_TIME]-[START_TIME],'day'),12,2))
||':'||to_number(substr(numtodsinterval([END_TIME]-[START_TIME],'day'),15,2))
from [TABLE_NAME];
回答by Ersin Gülbahar
You can not use DATEDIFFbut you can use this (if columns are not date type):
您不能使用DATEDIFF但您可以使用它（如果列不是日期类型）：
SELECT
to_date('2008-08-05','YYYY-MM-DD')-to_date('2008-06-05','YYYY-MM-DD')
AS DiffDate from dual
you can see the sample
你可以看到样本
回答by Davy Machado
You can use this:
你可以使用这个：
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
DECLARE
V_START_DATE CHAR(17) := '28/03/16 17:20:00';
V_END_DATE CHAR(17) := '30/03/16 17:50:10';
V_DATE_DIFF VARCHAR2(17);
BEGIN
SELECT
(TO_NUMBER( SUBSTR(NUMTODSINTERVAL(TO_DATE(V_END_DATE , 'DD/MM/YY HH24:MI:SS') - TO_DATE(V_START_DATE, 'DD/MM/YY HH24:MI:SS'), 'DAY'), 02, 9)) * 24) +
(TO_NUMBER( SUBSTR(NUMTODSINTERVAL(TO_DATE(V_END_DATE , 'DD/MM/YY HH24:MI:SS') - TO_DATE(V_START_DATE, 'DD/MM/YY HH24:MI:SS'), 'DAY'), 12, 2))) ||
SUBSTR(NUMTODSINTERVAL(TO_DATE(V_END_DATE , 'DD/MM/YY HH24:MI:SS') - TO_DATE(V_START_DATE, 'DD/MM/YY HH24:MI:SS'), 'DAY'), 14, 6) AS "HH24:MI:SS"
INTO V_DATE_DIFF
FROM
DUAL;
DBMS_OUTPUT.PUT_LINE(V_DATE_DIFF);
END;
回答by sandeep veeramalla
basically the to_char(sysdate,'DDD') returns no of days from 1-jan-yyyy to sysdate so that if subtract two dates it will return that,you will get difference between two dates
基本上 to_char(sysdate,'DDD') 返回从 1-jan-yyyy 到 sysdate 的天数，这样如果减去两个日期，它将返回那个，你会得到两个日期之间的差异
select to_char(sysdate,'DDD') -to_char(to_date('19-08-1995','dd-mm-yyyy'),'DDD') from dual;
select to_char(sysdate,'DDD') -to_char(to_date('19-08-1995','dd-mm-yyyy'),'DDD') from dual;
回答by Malcolm
Oracle DateDiff is from a different product, probably mysql (which is now owned by Oracle).
Oracle DateDiff 来自不同的产品，可能是 mysql（现在归 Oracle 所有）。
The difference between two dates (in oracle's usual database product) is in days (which can have fractional parts). Factor by 24 to get hours, 24*60 to get minutes, 24*60*60 to get seconds (that's as small as dates go). The math is 100% accurate for dates within a couple of hundred years or so. E.g. to get the date one second before midnight of today, you could say
两个日期之间的差异（在 oracle 的常用数据库产品中）以天为单位（可以有小数部分）。乘以 24 得到小时，乘以 24*60 得到分钟，乘以 24*60*60 得到秒（这和日期一样小）。对于几百年左右的日期，数学是 100% 准确的。例如，要在今天午夜前一秒获取日期，您可以说
select trunc(sysdate) - 1/24/60/60 from dual;
从双中选择 trunc(sysdate) - 1/24/60/60；
That means "the time right now", truncated to be just the date (i.e. the midnight that occurred this morning). Then it subtracts a number which is the fraction of 1 day that measures one second. That gives you the date from the previous day with the time component of 23:59:59.
这意味着“现在的时间”，被截断为日期（即今天早上发生的午夜）。然后它减去一个数字，它是 1 天的分数，它测量一秒。这为您提供了前一天的日期，时间分量为 23:59:59。
