I need to generate a list of week end dates based on the first week end date, and between a date range.

For example: I have a week end date of '06/04/2022'. If I enter 06/01/2022-01/01/2023 as my date parameters, I need a list of every seven days beginning on 06/04/2022 through 01/01/2023. The output would look something like this:

Note: the initial week end date is not necessarily always on a Saturday, so it would need to be based on the actual date and not the day of the week.

I have this code which produces every day between two dates, but I need every seven days between two dates, and based on a date field retrieved from another table. I'm stuck on how to get every seven days, or every week from the date.

select (date'2022-06-04'   level - 1) dt
from   dual
connect by level <= (date'2023-01-01' - date'2022-06-01'   1)

CodePudding user response：

Multiply the step size by 7:

SELECT DATE '2022-06-04'   (level - 1) * 7 AS dt
FROM   dual
CONNECT BY DATE '2022-06-04'   (level - 1) * 7 <= DATE '2023-01-01'

Or, to make the calculation you are preforming more obvious that you are adding a week, you can use an INTERVAL literal:

SELECT DATE '2022-06-04'   (level - 1) * INTERVAL '7' DAY AS dt
FROM   dual
CONNECT BY DATE '2022-06-04'   (level - 1) * INTERVAL '7' DAY <= DATE '2023-01-01'

CodePudding user response：

You could also calculate the number of weeks required in your connect by levels code.

i.e. This would give you the number of rows required

CONNECT BY LEVEL < (DATE2 - DATE1) / 7

Notably the DATE2 - DATE1 code works because Oracle likes to think of things in terms of days. i.e. Sysdate - 1 is just yesterday. Sysdate 1 is just tomorrow.

Then tack on a 1 since you probably want to return 1 row in the case it's less than a week

And then doing this

SELECT DATE'2023-01-24'   (LEVEL-1) * 7
FROM DUAL
CONNECT BY LEVEL < ((SYSDATE - DATE'2023-01-24') / 7)   1