SQL 如何在 Oracle 11g 中获取两个日期之间的天数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1646001/
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 can I get the number of days between 2 dates in Oracle 11g?
提问by Brian Ramsay
I'm trying to find an integer number of days between two dates in Oracle 11g.
我试图在 Oracle 11g 中找到两个日期之间的整数天数。
I can get close by doing
我可以通过做来接近
select sysdate - to_date('2009-10-01', 'yyyy-mm-dd') from dual
but this returns an interval, and I haven't been successful casting this to an integer.
但这会返回一个间隔，我还没有成功地将它转换为整数。
Edit: Apparently in 10g, this returns the number of days as an integer.
编辑：显然在 10g 中，这将天数作为整数返回。
采纳答案by Brian Ramsay
I figured it out myself. I need
我自己想通了。我需要
select extract(day from sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) from dual
回答by Tony Andrews
Or you could have done this:
或者你可以这样做：
select trunc(sysdate) - to_date('2009-10-01', 'yyyy-mm-dd') from dual
This returns a NUMBER of whole days:
这将返回 NUMBER 天：
SQL> create view v as
2 select trunc(sysdate) - to_date('2009-10-01', 'yyyy-mm-dd') diff
3 from dual;
View created.
SQL> select * from v;
DIFF
----------
29
SQL> desc v
Name Null? Type
---------------------- -------- ------------------------
DIFF NUMBER(38)
回答by Sabeen Malik
You can try using:
您可以尝试使用：
select trunc(sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) as days from dual
回答by Akki
This will work i have tested myself.
It gives difference between sysdate and date fetched from column admitdate
这将工作我已经测试过自己。
它给出了 sysdate 和从admitdate 列中获取的日期之间的差异
TABLE SCHEMA:
CREATE TABLE "ADMIN"."DUESTESTING"
(
"TOTAL" NUMBER(*,0),
"DUES" NUMBER(*,0),
"ADMITDATE" TIMESTAMP (6),
"DISCHARGEDATE" TIMESTAMP (6)
)
EXAMPLE:
select TO_NUMBER(trunc(sysdate) - to_date(to_char(admitdate, 'yyyy-mm-dd'),'yyyy-mm-dd')) from admin.duestesting where total=300
回答by Oleksiy
Full days between end of month and start of today, including the last day of the month:
SELECT LAST_DAY (TRUNC(SysDate)) - TRUNC(SysDate) + 1 FROM dualDays between using exact time:
SELECT SysDate - TO_DATE('2018-01-01','YYYY-MM-DD') FROM dual
月底和今天开始之间的全天，包括当月的最后一天：
SELECT LAST_DAY (TRUNC(SysDate)) - TRUNC(SysDate) + 1 FROM dual使用准确时间之间的天数：
SELECT SysDate - TO_DATE('2018-01-01','YYYY-MM-DD') FROM dual
