求日期间隔天数

显示全部楼层 · 2005-2-28 12:57:00

SQL> r
1* select to_date(sysdate,'yyyy-mm-dd')-to_date('2002-11-01','yyyy-mm-dd') from dual
TO_DATE(SYSDATE,'YYYY-MM-DD')-TO_DATE('2002-11-01','YYYY-MM-DD')
----------------------------------------------------------------

-723516

???

另搜索贴子，看不懂：
SQL> select (to_date('2002-10-10 14:40','yyyy-mm-dd hh24:mi')-to_date('2002-10-10 10:20','yyyy-mm-dd
hh24:mi')) * 24 from dual;
(TO_DATE('2002-10-1014:40','YYYY-MM-DDHH24:MI')-TO_DATE('2002-10-1010:20','YYYY-
--------------------------------------------------------------------------------

4.33333333

自己试验：不懂
SQL> r
1select (to_date(sysdate,'yyyy-mm-dd hh24:mi')-to_date('2002-10-10 10:20','yyyy-mm-dd
2*hh24:mi')) * 24 from dual
(TO_DATE(SYSDATE,'YYYY-MM-DDHH24:MI')-TO_DATE('2002-10-1010:20','YYYY-MM-DDHH24:
--------------------------------------------------------------------------------

-17363866

千问 · 2005-2-28 12:57:00

SQL> select months_between(sysdate,sysdate-30) from dual;
MONTHS_BETWEEN(SYSDATE,SYSDATE-30)

----------------------------------

1

千问 · 2005-2-28 12:57:00

to_date(sysdate,'yyyy-mm-dd')会报错的，记住两个日期相减是天.

千问 · 2005-2-28 12:57:00

select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-to_date('2002-11-01','yyyy-mm-dd') from dual

千问 · 2005-2-28 12:57:00

最初由 ccbzzp 发布
[B]select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-to_date('2002-11-01','yyyy-mm-dd') from dual [/B]
select sysdate-to_date('2002-11-01','yyyy-mm-dd') from dual
这样不就可以了，为什么还要to_date(sysdate,'yyyy-mm-dd') 呢。

千问 · 2005-2-28 12:57:00

1.return days between day2 and day1
SQL> select (to_date('2002-10-10 11:00','yyyy-mm-dd hh24:mi')-to_date('2002-10-10
210:00','yyyy-mm-dd
3hh24:mi')) from dual;
(TO_DATE('2002-10-1011:00','YYYY-MM-DDHH24:MI')-TO_DATE('2002-10-1010:00','YYYY-
--------------------------------------------------------------------------------

.041666667

2.return hours between day2 and day1
SQL> select (to_date('2002-10-10 11:00','yyyy-mm-dd hh24:mi')-to_date('2002-10-10
210:00','yyyy-mm-dd
3hh24:mi')) * 24 from dual;
(TO_DATE('2002-10-1011:00','YYYY-MM-DDHH24:MI')-TO_DATE('2002-10-1010:00','YYYY-
--------------------------------------------------------------------------------

1

3.return miniutes between day2 and day1
SQL> select (to_date('2002-10-10 11:00','yyyy-mm-dd hh24:mi')-to_date('2002-10-10
210:00','yyyy-mm-dd
3hh24:mi')) * 24*60 from dual;
(TO_DATE('2002-10-1011:00','YYYY-MM-DDHH24:MI')-TO_DATE('2002-10-1010:00','YYYY-
--------------------------------------------------------------------------------

60