Oracle: Select dates between date ranges using SQL

Question

I have a table MY_DATES (START_DATE DATE, END_DATE DATE) with data like :

START_DATE        END_DATE
---------------------------
18-DEC-17         07-JAN-18
27-JAN-18         06-FEB-18
08-MAR-18         18-MAR-18

I need to generate dates for all the date ranges in my table in a single column using SQL like below:

DATES 
---------- 
18-DEC-17 
19-DEC-17 
20-DEC-17 
. 
. 
. 
06-JAN-18 
07-JAN-18 
27-JAN-18 
28-JAN-18 
29-JAN-18 
. 
. 
. 
05-FEB-18 
06-FEB-18 
08-MAR-18 
09-MAR-18 
10-MAR-18 
. 
. 
. 
18-MAR-18

I am using oracle 11G. appreciate any help in this regard.

I couldn't find an SO answer, but here is a post on Ask TOM that addresses this need for two static dates. It could probably be adapted to pull the start and end from a table. — Chris Hep
– Chris Hep, Commented Mar 19, 2018 at 19:55

Kaushik Nayak · Accepted Answer · 2018-03-20 09:42:42Z

2

Try this.

WITH t (sdt, ldt) AS (SELECT MIN (START_DATE), MAX (END_DATE) FROM MY_DATES)
SELECT *
  FROM (    SELECT sdt + LEVEL - 1 AS dates
              FROM t
        CONNECT BY LEVEL <= ldt - sdt + 1) c
 WHERE EXISTS
          (SELECT 1
             FROM MY_DATES d
            WHERE c.dates BETWEEN START_DATE AND END_DATE);

Demo

answered Mar 20, 2018 at 9:42

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2018-03-19 20:31:34Z

1

Tro to do this:

with calendar as (
    select :startdate + rownum - 1 as day
    from dual
    connect by rownum < :enddate - :startdate
)
select rownum as "S.No", 
       to_date(day,'dd_mm_yyyy') as "Cal_Dt", 
       to_char(day,'day') as "DayName"
from calendar

where You have to replace :startdate and :enddate with meaningful values for your particular case.

edited Mar 19, 2018 at 20:31

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

answered Mar 19, 2018 at 20:06

Julius Rachmanas

818 bronze badges

Comments

ZF007 · Accepted Answer · 2019-08-08 01:07:21Z

In this user-case, AVG fees are generated and distributed across all the end of month dates in a date range.

CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)

  Insert into #ProductSales
  Values
  (373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
  (375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')

  ;WITH Dates AS (
        SELECT ProjectiD
        ,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
         ,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
         FROM #ProductSales
        UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
         [Date] = DATEADD(MONTH, 1, [Date])
        FROM
         Dates
        WHERE
         Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
 CAST([Date] as Date) Date
FROM
 Dates
 OPTION (MAXRECURSION 45)

Collectives™ on Stack Overflow

Oracle: Select dates between date ranges using SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related