oracle 计算并查询两个日期所横跨的天数列表、月数列表、年数列表（connect by）

1.情景展示

　　现在，已知两个日期，根据它俩查询所间隔的所有日期列表，如何实现？ 

2.原因分析

　　使用递归查询connect by来实现

3.解决方案

　　3.1 查询出间隔的天数列表

         SELECT TO_CHAR(TO_DATE(         '2020-06-01' ,          'YYYY-MM-DD' ) + ROWNUM - 1,
               'YYYY-MM-DD' )          AS REGDATE
  FROM DUAL
CONNECT BY ROWNUM <=
           TRUNC(TO_DATE(         '2020-06-17' ,          'YYYY-MM-DD' ) -
                 TO_DATE(         '2020-06-01' ,          'YYYY-MM-DD' )) + 1
 ORDER BY REGDATE　

　　解说：

　　trunc(date2-date1)，得出的是两个日期间隔的整数位数，比方说17-1=16，而我们要查询的是这两个日期所跨的所有时间，所需需要+1，这样，才能包括开始时间date1和结尾date2；

　　connect by，准确的来说是递归查询SQL，connect by rownum <=17，也就是需要查询17次；

　　TO_DATE('2020-06-01', 'YYYY-MM-DD') + ROWNUM - 1，因为rownum是从1开始的，比方说，第一次查询rownum=1，查询出来的日期是6月1号+1=6月2号，实际上该为6月1号，第二次rownum=2，查询出来的日期是6月1号+2=6月3号，实际为6月2号，。。。所以需要-1。

　　3.2 查询出间隔的月份列表

         --查询两个日期所跨过的月份列表
SELECT DISTINCT REGDATE
  FROM (         SELECT TO_CHAR(TO_DATE(         '2020-01' ,          'YYYY-MM' ) + ROWNUM - 1,          'YYYY-MM' )          AS REGDATE
          FROM DUAL
        CONNECT BY ROWNUM <= TO_DATE(         '2020-06' ,          'YYYY-MM' ) -
                   TO_DATE(         '2020-01' ,          'YYYY-MM' ) + 1)
 ORDER BY REGDATE

　　两个date日期类型相减，得出的是相差的天数

　　横跨153天，也就是最终查询结果是153条数据，涵盖了2020-01到2020-06，需要去重，所以，使用distinct；

　　如果不想用distinct，当然也可以用group by来实现。

　　错误实现方式：

　　使用months_between()计算两个日期间的间隔月数+1，其值为6，这个没有问题；

　　问题处在了：TO_CHAR(TO_DATE('2020-01', 'YYYY-MM') + ROWNUM - 1, 'YYYY-MM')，我们将日期格式化到天，看一下 

　　日期+数值，代表的含义是：往指定日期后叠加多少天，也就是不管你数值有多大n，都按天数来计算，并推算出n天后对应的日期。

　　这样一来，自然是不能再使用months_between()来计算，即使，这个函数本身能够计算出间隔月数。

　　3.3  查询出间隔的年份列表

　　明白了天数和月数的查询计算方式，这个也就是直接套模板的事儿了。 

　　3.4 查询月初和月末所对应的日期

         SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -         LEVEL - 1),          'YYYYMMDD' )          AS BEGINTIME,
       TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -         LEVEL ),          'YYYYMMDD' ) ENDTIME
  FROM DUAL
CONNECT BY LEVEL < 13

　　解说： 

　　现在是2020年6月17日，last_day()，求出6月最后一天是30日，+1，得到的是7月1号，假设现在level=1，add_months(7月1日,-2)，也就是5月1日；

　　ADD_MONTHS(LAST_DAY(SYSDATE), -LEVEL)，也就是add_months(6月30日,-1)，也就是5月31日（一个月前）。

2023年8月8日16:08:54

3.5根据日期计算年龄

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, T1.BIRTH_DAY) / 12) AGE
  FROM WJ_PATIENT_INFO T1

 

posted @ 2020-06-17 15:36  Marydon  阅读(951)  评论(0)  编辑  收藏  举报