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

Heaven helps those who help themselves
资深码农+深耕理财=财富自由
欢迎关注

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

Created by Marydon on 2020-06-17 15:36

1.情景展示

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

2.原因分析

　　使用递归查询connect by来实现

3.解决方案

　　3.1 查询出间隔的天数列表

?

1 2 3 4 5 6 7

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 查询出间隔的月份列表

?

1 2 3 4 5 6 7

--查询两个日期所跨过的月份列表 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 查询月初和月末所对应的日期

?

1 2 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

 

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• 个人主页
• oracle 存储过程 示例
• java 递归转非递归（树形转非树形）
• java 递归实现树形结构的两种实现方式
• oracle 递归查询实现树形结构
• Oracle trunc()、interval、extract()处理日期类型

与君共勉：最实用的自律是攒钱，最养眼的自律是健身，最健康的自律是早睡，最改变气质的自律是看书，最好的自律是经济独立 。

您的一个点赞，一句留言，一次打赏，就是博主创作的动力源泉！

↓↓↓↓↓↓写的不错，对你有帮助？赏博主一口饭吧↓↓↓↓↓↓