日期时间操作总结

①两个日期时间相差多少秒

SELECT SUBSTRB(TO_CHAR(batchstart,'YYYY-MM-DD HH24:MI:SS.FF'),1,23) batchstart
     , SUBSTRB(TO_CHAR(batchend,'YYYY-MM-DD HH24:MI:SS.FF'),1,23)   batchend
     , EXTRACT(DAY     FROM batchend-batchstart) * 86400
     + EXTRACT(HOUR    FROM batchend-batchstart) *  3600
     + EXTRACT(MINUTE  FROM batchend-batchstart) *    60
     + EXTRACT(SECOND  FROM batchend-batchstart) AS time
FROM DUAL

 

②两个日期相差多少年，多少月的算出

　SELECT   CASE WHEN '20110531' > '20100601'
         THEN TRUNC(((SUBSTRB('20110531',1,4)-SUBSTRB('20100601',1,4))*12 + (SUBSTRB('20110531',5,2)-SUBSTRB('20100601',5,2))+1)/12)
         ELSE 0
         END YEAR_CNT,
         CASE WHEN '20150228' > '20100601'
         THEN MOD((SUBSTRB('20150228',1,4)-SUBSTRB('20100601',1,4))*12 + (SUBSTRB('20150228',5,2)-SUBSTRB('20100601',5,2)+1),12)
         ELSE 0
         END MONTH_CNT
     FROM dual