oracle 两个日期之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4784961/
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
Difference between two dates
提问by ziggy
I have a table that has the following data
我有一个包含以下数据的表
fromDate | toDate
20JAN11 | 29DEC30
Both dates are for the 21st Century (i.e. 2011 and 2030) but only the last two characters are stored.
两个日期都是 21 世纪（即 2011 和 2030），但只存储最后两个字符。
Why is the following statement (when run from within a PL/SQL module) against the above data always returns a positive value
为什么针对上述数据的以下语句（当从 PL/SQL 模块中运行时）总是返回正值
dateDifference := (fromDate - toDate)
If i run the following statement from sqlplus i get the correct negative value which is correct.
如果我从 sqlplus 运行以下语句，我会得到正确的负值。
select to_date('20JAN11','DDMONYY')-to_Date('29DEC30','DDMONYY') from dual;
I remember reading somewhere that Oracle would sometimes use the wrong century but i dont quite remember the exact scenario where that would happen.
我记得在某处读到 Oracle 有时会使用错误的世纪，但我不太记得会发生这种情况的确切场景。
回答by Dave Costa
Assuming those columns are of DATEdatatype, which seems to be the case: Oracle always stores DATEvalues in an internal format which includes the full year. The fact that you are seeing only a 2-digit year has to do with the date format used to convert the date to a string for display. So most likely the stored century values are not what you think they are.
假设这些列是DATE数据类型，情况似乎是这样：Oracle 总是DATE以包含全年的内部格式存储值。您只看到 2 位数年份这一事实与用于将日期转换为字符串以进行显示的日期格式有关。因此，存储的世纪值很可能不是您认为的那样。
Try selecting the dates with an explicit format to see what you really have stored:
尝试选择具有明确格式的日期以查看您真正存储的内容：
SELECT TO_CHAR( fromDate, 'DD-MON-YYYY' ), TO_CHAR( toDate, 'DD-MON-YYYY' )
回答by DCookie
Seems to work for me either way on my 10g database:
似乎在我的 10g 数据库上对我有用：
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 d1 DATE := to_date('20JAN11','DDMONRR');
3 d2 DATE := to_date('29DEC30','DDMONRR');
4 diff INTEGER;
5 BEGIN
6 diff := d1 - d2;
7 dbms_output.put_line(diff);
8 END;
9 /
-7283
PL/SQL procedure successfully completed
SQL>
EDIT: works for YY instead of RR year format as well.
编辑：也适用于 YY 而不是 RR 年份格式。
EDIT2: Something like this, you mean?
EDIT2：像这样的东西，你的意思是？
SQL> create table t (d1 date, d2 date);
Table created
SQL> insert into t values (to_date('20JAN11','DDMONYY'), to_date('29DEC30','DDMONYY'));
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> DECLARE
2 R t%ROWTYPE;
3 diff INTEGER;
4 BEGIN
5 SELECT d1, d2
6 INTO R
7 FROM t;
8 diff := R.d1 - R.d2;
9 dbms_output.put_line(diff);
10 END;
11 /
-7283
PL/SQL procedure successfully completed
SQL>
As @Alex states, you may want to verify your data.
正如@Alex 所说，您可能想要验证您的数据。
回答by Harrison
works without formatting as well
无需格式化也能工作
CREATE TABLE DATETEST(FROMDATE DATE, TODATE DATE);
insert into DATETEST (fromdate,todate) values (to_date('20Jan11','ddMonrr'),to_date('29DEC30','ddMonrr'));
SELECT TO_CHAR(FROMDATE,'ddMonrrrr hh24:mi:ss') FROMDATE,
TO_CHAR(TODATE,'ddMonrrrr hh24:mi:ss') TODATE
from datetest ;
/*
FROMDATE TODATE
------------------ ------------------
20Jan2011 00:00:00 29Dec2030 00:00:00
*/
set serveroutput on
DECLARE
l_FROMDATE DATETEST.FROMDATE%type ;
L_TODATE DATETEST.TODATE%TYPE;
dateDifference number;
BEGIN
--notice -- no formatting just putting them into a variable for test
SELECT FROMDATE, TODATE
INTO L_FROMDATE, L_TODATE
from datetest;
DATEDIFFERENCE := L_FROMDATE - L_TODATE ;
DBMS_OUTPUT.PUT_LINE('DATEDIFFERENCE = ' || DATEDIFFERENCE );
end ;
--DATEDIFFERENCE = -7283
SELECT FROMDATE-TODATE
from datetest ;
/* --still not formatting
FROMDATE-TODATE
----------------------
-7283
*/
SELECT (FROMDATE - TODATE) DATEDIFF,
TO_CHAR(FROMDATE,'ddMonrrrr') FROMDATE,
to_char(todate,'ddMonrrrr') todate
from (
SELECT TO_DATE('20JAN11','DDMONYY') FROMDATE,
TO_DATE('29DEC30','DDMONYY') TODATE
FROM DUAL)
;
/*
DATEDIFF FROMDATE TODATE
---------------------- --------- ---------
-7283 20Jan2011 29Dec2030
*/
try running the first query on your table:
尝试在您的表上运行第一个查询：
SELECT TO_CHAR(FROMDATE,'ddMonrrrr hh24:mi:ss') FROMDATE,
TO_CHAR(TODATE,'ddMonrrrr hh24:mi:ss') TODATE
from datetest ;
see if the years are what you actually expect.
看看这些年是否是你真正期望的。
(Edit: changed to use two digit years)
（编辑：改为使用两位数年份）
