生成两个日期之间的日期 [英] Generating dates between two dates

问题描述

我需要在两个给定日期之间生成所有日期。只要有一个日期范围就可以正常工作。但是，如果我有多个日期范围，则此解决方案不起作用。我在这里搜索了asktom，但找不到相关的指针/解决方案。

I need to generate all dates between two given dates. This works fine as long as there is just one date range. However, if I have multiple date ranges, this solution doesn't work. I have searched here as well as on asktom, but couldn't locate relevant pointers/solution.

我使用all_objects和CONNECT BY ROWNUM尝试了两个解决方案，但没有运气。以下是问题陈述： sqlfiddle

I tried both the solutions using all_objects and CONNECT BY ROWNUM, but no luck. Here is the problem statement: sqlfiddle

输入

ID  START_DATE      END_DATE 
101 April, 01 2013  April, 10 2013 
102   May, 10 2013    May, 12 2013

输出

ID  Dates
101 April, 01 2013 
101 April, 02 2013  
101 April, 03 2013  
101 April, 04 2013  
101 April, 05 2013  
101 April, 06 2013  
101 April, 07 2013  
101 April, 08 2013  
101 April, 09 2013  
101 April, 10 2013  
102   May, 10 2013  
102   May, 11 2013  
102   May, 12 2013

推荐答案

select 
  A.ID, 
  A.START_DATE+delta dt
from 
  t_dates A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(end_date - start_date) from t_dates
     )
  )
where A.START_DATE+delta <= A.end_date
order by 1, 2

这篇关于生成两个日期之间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！