Menu
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.
I tried both the solutions using all_objects and CONNECT BY ROWNUM, but no luck. Here is the problem statement: sqlfiddle
Input
ID START_DATE END_DATE
101 April, 01 2013 April, 10 2013
102 May, 10 2013 May, 12 2013
Output
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
903
Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another.
To find the number of days between these two dates, you can enter “=B2-B1” (without the quotes into cell B3). Once you hit enter, Excel will automatically calculate the number of days between the two dates entered. Note that Excel recognizes leap years.
DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106'; SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a. object_id) - 1, @MinDate) FROM sys. all_objects a CROSS JOIN sys.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
