CODEWITHSUNDEEP
sqloracle-databasedatetimeoracle11goracle10g
Samar.Abdeen
Samar.Abdeen

Reputation: 47

Generate Dates in oracle with gaps during Time Period

I Need To Generate Dates During Specific Time Period ( Using Oracle SQL Developer 12& 18). / I Found Bellow Solution:

SELECT TO_DATE('01/01/2020','DD/MM/YYYY') + (ROWNUM - 1) DATEGEN
FROM ALL_OBJECTS
WHERE TO_DATE('01/01/2020','DD/MM/YYYY') + (ROWNUM -1) <=  TO_DATE('01/11/2020','DD/MM/YYYY')

I Get Daily Dates (As Bellow):

01-JAN-20 12.00.00 AM 
02-JAN-20 12.00.00 AM 
03-JAN-20 12.00.00 AM 
04-JAN-20 12.00.00 AM 
05-JAN-20 12.00.00 AM 
06-JAN-20 12.00.00 AM

I Need To Get Dates With Gap 5 days or 3 days (Example):

01-JAN-20 12.00.00 AM

05-JAN-20 12.00.00 AM

10-JAN-20 12.00.00 AM

15-JAN-20 12.00.00 AM

Any Suggestions?! Thanks in Advance.

Upvotes: 0

Views: 101

Answers (2)

GMB
GMB

Reputation: 222622

You could use a standad recursive query:

with cte (dt) as (
    select date '2020-01-01' from dual
    union all
    select dt + interval '5' day from cte where dt < date '2020-01-15'
)
select * from cte order by dt

You can adjust the boundaries as needed, or move them to another cte:

with 
    params (startdt, enddt) as (
        select date '2020-01-01', date '2020-01-15' from dual
    ),
    cte (dt, enddt) as (
        select startdt dt, enddt from params
        union all
        select dt + interval '5' day, enddt from cte where dt < enddt
    )
select dt from cte order by dt

Note that the intervals between the dates are not constant in your sample data (there are 4 days between the first two dates, then 5 days.

You might want to adjust the where clause of the recursive member depending on your actual requirement. Here, the last day may be greater the January 15th. If you don't want that, then change the where clause to:

where dt + interval '5' day < enddt

Upvotes: 2

Popeye
Popeye

Reputation: 35920

You can use the hiearchy query with simple logic as follows:

SELECT DATE '2020-01-01' + ( LEVEL * 5 ) - 1
  FROM DUAL CONNECT BY
    LEVEL <= ( DATE '2020-11-01' - DATE '2020-01-01' ) / 5;

Please note that in your question first two dates have 4 days of difference and in my solution, the First date will be 05-01-2020

Upvotes: 2

Related Questions