Create Dates Oracle

Question

I am trying to create a table with a list of start dates and end dates where the start date is monday and the week end date is sunday, or start and end at the beginning and end of the month for years. Currently between 2014 and 2020.

The code I have been trying to get to work for the weekly records, at best, goes to the end of 2014 and won't go further

select 'WEEKLY' CALNAME
    ,'WEEKLY' TYPE
    ,'WEEKLY'||' '||to_char(gen.d,'IW') DESCR
    ,to_char(gen.d,'IW') Period
     , min(gen.d) StartDate
     , min(gen.d) +6 EndDate
  from (
         select to_date('01.01.2014','DD.MM.YYYY') + level -1 d
           from dual
        connect by level <= 365 -- approx. number of days
       ) gen
 group by
       to_char(gen.d,'IW')
     --, to_char(gen.d,'YYYY MM IW')
having min(gen.d) <= to_date('31.12.2020','DD.MM.YYYY')
order by min(gen.d);

Your question is not clear. The intervals all begin on a Monday, and they end "at the end of the week" (is that Sunday? If so, why don't you just say Sunday?) or the end of the month. OK, then what happens to the BEGINNING of a month, if it is not a Monday? Do you leave it out? — user5683823
– user5683823, Commented Apr 6, 2018 at 19:54
Sorry, for the end of the week, it's Sunday. Not sure why I worded it so poorly. For the monthly version, I just want it to start and end on the first and last day of the month — baskinsr
– baskinsr, Commented Apr 6, 2018 at 19:55
Oh - so it's just for two separate calendars, one weekly and the other monthly? Then the monthly calendar is clear. For the weekly, January 1 2014 is a Wednesday. Do you need a period from Wednesday to Sunday (five days), then full weeks, and then possibly another fractional week at the end of 2020? Or should the calendar only show FULL weeks, perhaps beginning before January 1 2014 / ending after December 31 2020? — user5683823
– user5683823, Commented Apr 6, 2018 at 20:05
Wait - looking at your code, you are doing more than just generating these periods. The GROUP BY is definitely incorrect; if you want to group by week, then GROUP BY TRUNC(gen.d, 'IW'). If that is your real question, it is very unclear in your problem description - it only appears in the code, not in your explanation. — user5683823
– user5683823, Commented Apr 6, 2018 at 20:08
Yeah, I didn't think about the fact that I was grouping the weeks each year together, and I needed to add the year to my grouping. To your other question, I would ideally like it to start before Jan 1 2014/end after Dec 31 2020 if the week works that way. Also, the weeks that cross over years in between, I can't get them to work properly, either — baskinsr
– baskinsr, Commented Apr 6, 2018 at 20:20

MT0 · Accepted Answer · 2018-04-06 20:28:16Z

Its not clear what you expect at year boundaries - this will stop at the end of the calendar year (not the ISO year) and then make another row for the rest of the ISO week in the next calendar year:

SQL Fiddle

Oracle 11g R2 Schema Setup:

SELECT 1 FROM DUAL;

Query 1:

SELECT 'WEEKLY ' || TO_CHAR( dt, 'IYYY-IW' ) AS DESCR,
       GREATEST(
         TRUNC( dt, 'IW' ),
         TRUNC( dt, 'YYYY' )
       ) AS StartDt,
       LEAST(
         TRUNC( dt, 'IW' ) + INTERVAL '6' DAY,
         TRUNC( dt, 'YYYY' ) + INTERVAL '1' YEAR - INTERVAL '1' DAY
       ) AS EndDt
FROM   (
  SELECT ADD_MONTHS(
           DATE '2014-01-01',
           12 * FLOOR( ( LEVEL - 1 ) / 53 )
         )  + MOD( LEVEL - 1, 53 ) * 7 AS dt
  FROM   DUAL
  CONNECT BY LEVEL <= 53 * 6
)

Results:

|          DESCR |              STARTDT |                ENDDT |
|----------------|----------------------|----------------------|
| WEEKLY 2014-01 | 2014-01-01T00:00:00Z | 2014-01-05T00:00:00Z |
| WEEKLY 2014-02 | 2014-01-06T00:00:00Z | 2014-01-12T00:00:00Z |
| WEEKLY 2014-03 | 2014-01-13T00:00:00Z | 2014-01-19T00:00:00Z |
| WEEKLY 2014-04 | 2014-01-20T00:00:00Z | 2014-01-26T00:00:00Z |
| WEEKLY 2014-05 | 2014-01-27T00:00:00Z | 2014-02-02T00:00:00Z |
| WEEKLY 2014-06 | 2014-02-03T00:00:00Z | 2014-02-09T00:00:00Z |
| WEEKLY 2014-07 | 2014-02-10T00:00:00Z | 2014-02-16T00:00:00Z |
| WEEKLY 2014-08 | 2014-02-17T00:00:00Z | 2014-02-23T00:00:00Z |
| WEEKLY 2014-09 | 2014-02-24T00:00:00Z | 2014-03-02T00:00:00Z |
| WEEKLY 2014-10 | 2014-03-03T00:00:00Z | 2014-03-09T00:00:00Z |
| WEEKLY 2014-11 | 2014-03-10T00:00:00Z | 2014-03-16T00:00:00Z |
| WEEKLY 2014-12 | 2014-03-17T00:00:00Z | 2014-03-23T00:00:00Z |
| WEEKLY 2014-13 | 2014-03-24T00:00:00Z | 2014-03-30T00:00:00Z |
| WEEKLY 2014-14 | 2014-03-31T00:00:00Z | 2014-04-06T00:00:00Z |
| WEEKLY 2014-15 | 2014-04-07T00:00:00Z | 2014-04-13T00:00:00Z |
| WEEKLY 2014-16 | 2014-04-14T00:00:00Z | 2014-04-20T00:00:00Z |
| WEEKLY 2014-17 | 2014-04-21T00:00:00Z | 2014-04-27T00:00:00Z |
| WEEKLY 2014-18 | 2014-04-28T00:00:00Z | 2014-05-04T00:00:00Z |
| WEEKLY 2014-19 | 2014-05-05T00:00:00Z | 2014-05-11T00:00:00Z |
| WEEKLY 2014-20 | 2014-05-12T00:00:00Z | 2014-05-18T00:00:00Z |
| WEEKLY 2014-21 | 2014-05-19T00:00:00Z | 2014-05-25T00:00:00Z |
| WEEKLY 2014-22 | 2014-05-26T00:00:00Z | 2014-06-01T00:00:00Z |
| WEEKLY 2014-23 | 2014-06-02T00:00:00Z | 2014-06-08T00:00:00Z |
| WEEKLY 2014-24 | 2014-06-09T00:00:00Z | 2014-06-15T00:00:00Z |
| WEEKLY 2014-25 | 2014-06-16T00:00:00Z | 2014-06-22T00:00:00Z |
| WEEKLY 2014-26 | 2014-06-23T00:00:00Z | 2014-06-29T00:00:00Z |
| WEEKLY 2014-27 | 2014-06-30T00:00:00Z | 2014-07-06T00:00:00Z |
| WEEKLY 2014-28 | 2014-07-07T00:00:00Z | 2014-07-13T00:00:00Z |
| WEEKLY 2014-29 | 2014-07-14T00:00:00Z | 2014-07-20T00:00:00Z |
| WEEKLY 2014-30 | 2014-07-21T00:00:00Z | 2014-07-27T00:00:00Z |
| WEEKLY 2014-31 | 2014-07-28T00:00:00Z | 2014-08-03T00:00:00Z |
| WEEKLY 2014-32 | 2014-08-04T00:00:00Z | 2014-08-10T00:00:00Z |
| WEEKLY 2014-33 | 2014-08-11T00:00:00Z | 2014-08-17T00:00:00Z |
| WEEKLY 2014-34 | 2014-08-18T00:00:00Z | 2014-08-24T00:00:00Z |
| WEEKLY 2014-35 | 2014-08-25T00:00:00Z | 2014-08-31T00:00:00Z |
| WEEKLY 2014-36 | 2014-09-01T00:00:00Z | 2014-09-07T00:00:00Z |
| WEEKLY 2014-37 | 2014-09-08T00:00:00Z | 2014-09-14T00:00:00Z |
| WEEKLY 2014-38 | 2014-09-15T00:00:00Z | 2014-09-21T00:00:00Z |
| WEEKLY 2014-39 | 2014-09-22T00:00:00Z | 2014-09-28T00:00:00Z |
| WEEKLY 2014-40 | 2014-09-29T00:00:00Z | 2014-10-05T00:00:00Z |
| WEEKLY 2014-41 | 2014-10-06T00:00:00Z | 2014-10-12T00:00:00Z |
| WEEKLY 2014-42 | 2014-10-13T00:00:00Z | 2014-10-19T00:00:00Z |
| WEEKLY 2014-43 | 2014-10-20T00:00:00Z | 2014-10-26T00:00:00Z |
| WEEKLY 2014-44 | 2014-10-27T00:00:00Z | 2014-11-02T00:00:00Z |
| WEEKLY 2014-45 | 2014-11-03T00:00:00Z | 2014-11-09T00:00:00Z |
| WEEKLY 2014-46 | 2014-11-10T00:00:00Z | 2014-11-16T00:00:00Z |
| WEEKLY 2014-47 | 2014-11-17T00:00:00Z | 2014-11-23T00:00:00Z |
| WEEKLY 2014-48 | 2014-11-24T00:00:00Z | 2014-11-30T00:00:00Z |
| WEEKLY 2014-49 | 2014-12-01T00:00:00Z | 2014-12-07T00:00:00Z |
| WEEKLY 2014-50 | 2014-12-08T00:00:00Z | 2014-12-14T00:00:00Z |
| WEEKLY 2014-51 | 2014-12-15T00:00:00Z | 2014-12-21T00:00:00Z |
| WEEKLY 2014-52 | 2014-12-22T00:00:00Z | 2014-12-28T00:00:00Z |
| WEEKLY 2015-01 | 2014-12-29T00:00:00Z | 2014-12-31T00:00:00Z |
| WEEKLY 2015-01 | 2015-01-01T00:00:00Z | 2015-01-04T00:00:00Z |
| WEEKLY 2015-02 | 2015-01-05T00:00:00Z | 2015-01-11T00:00:00Z |
| WEEKLY 2015-03 | 2015-01-12T00:00:00Z | 2015-01-18T00:00:00Z |
| WEEKLY 2015-04 | 2015-01-19T00:00:00Z | 2015-01-25T00:00:00Z |
| WEEKLY 2015-05 | 2015-01-26T00:00:00Z | 2015-02-01T00:00:00Z |
| WEEKLY 2015-06 | 2015-02-02T00:00:00Z | 2015-02-08T00:00:00Z |
| WEEKLY 2015-07 | 2015-02-09T00:00:00Z | 2015-02-15T00:00:00Z |
| WEEKLY 2015-08 | 2015-02-16T00:00:00Z | 2015-02-22T00:00:00Z |
| WEEKLY 2015-09 | 2015-02-23T00:00:00Z | 2015-03-01T00:00:00Z |
| WEEKLY 2015-10 | 2015-03-02T00:00:00Z | 2015-03-08T00:00:00Z |
| WEEKLY 2015-11 | 2015-03-09T00:00:00Z | 2015-03-15T00:00:00Z |
| WEEKLY 2015-12 | 2015-03-16T00:00:00Z | 2015-03-22T00:00:00Z |
| WEEKLY 2015-13 | 2015-03-23T00:00:00Z | 2015-03-29T00:00:00Z |
| WEEKLY 2015-14 | 2015-03-30T00:00:00Z | 2015-04-05T00:00:00Z |
| WEEKLY 2015-15 | 2015-04-06T00:00:00Z | 2015-04-12T00:00:00Z |
| WEEKLY 2015-16 | 2015-04-13T00:00:00Z | 2015-04-19T00:00:00Z |
| WEEKLY 2015-17 | 2015-04-20T00:00:00Z | 2015-04-26T00:00:00Z |
| WEEKLY 2015-18 | 2015-04-27T00:00:00Z | 2015-05-03T00:00:00Z |
| WEEKLY 2015-19 | 2015-05-04T00:00:00Z | 2015-05-10T00:00:00Z |
| WEEKLY 2015-20 | 2015-05-11T00:00:00Z | 2015-05-17T00:00:00Z |
| WEEKLY 2015-21 | 2015-05-18T00:00:00Z | 2015-05-24T00:00:00Z |
| WEEKLY 2015-22 | 2015-05-25T00:00:00Z | 2015-05-31T00:00:00Z |
| WEEKLY 2015-23 | 2015-06-01T00:00:00Z | 2015-06-07T00:00:00Z |
| WEEKLY 2015-24 | 2015-06-08T00:00:00Z | 2015-06-14T00:00:00Z |
| WEEKLY 2015-25 | 2015-06-15T00:00:00Z | 2015-06-21T00:00:00Z |
| WEEKLY 2015-26 | 2015-06-22T00:00:00Z | 2015-06-28T00:00:00Z |
| WEEKLY 2015-27 | 2015-06-29T00:00:00Z | 2015-07-05T00:00:00Z |
| WEEKLY 2015-28 | 2015-07-06T00:00:00Z | 2015-07-12T00:00:00Z |
| WEEKLY 2015-29 | 2015-07-13T00:00:00Z | 2015-07-19T00:00:00Z |
| WEEKLY 2015-30 | 2015-07-20T00:00:00Z | 2015-07-26T00:00:00Z |
| WEEKLY 2015-31 | 2015-07-27T00:00:00Z | 2015-08-02T00:00:00Z |
| WEEKLY 2015-32 | 2015-08-03T00:00:00Z | 2015-08-09T00:00:00Z |
| WEEKLY 2015-33 | 2015-08-10T00:00:00Z | 2015-08-16T00:00:00Z |
| WEEKLY 2015-34 | 2015-08-17T00:00:00Z | 2015-08-23T00:00:00Z |
| WEEKLY 2015-35 | 2015-08-24T00:00:00Z | 2015-08-30T00:00:00Z |
| WEEKLY 2015-36 | 2015-08-31T00:00:00Z | 2015-09-06T00:00:00Z |
| WEEKLY 2015-37 | 2015-09-07T00:00:00Z | 2015-09-13T00:00:00Z |
| WEEKLY 2015-38 | 2015-09-14T00:00:00Z | 2015-09-20T00:00:00Z |
| WEEKLY 2015-39 | 2015-09-21T00:00:00Z | 2015-09-27T00:00:00Z |
| WEEKLY 2015-40 | 2015-09-28T00:00:00Z | 2015-10-04T00:00:00Z |
| WEEKLY 2015-41 | 2015-10-05T00:00:00Z | 2015-10-11T00:00:00Z |
| WEEKLY 2015-42 | 2015-10-12T00:00:00Z | 2015-10-18T00:00:00Z |
| WEEKLY 2015-43 | 2015-10-19T00:00:00Z | 2015-10-25T00:00:00Z |
| WEEKLY 2015-44 | 2015-10-26T00:00:00Z | 2015-11-01T00:00:00Z |
| WEEKLY 2015-45 | 2015-11-02T00:00:00Z | 2015-11-08T00:00:00Z |
| WEEKLY 2015-46 | 2015-11-09T00:00:00Z | 2015-11-15T00:00:00Z |
| WEEKLY 2015-47 | 2015-11-16T00:00:00Z | 2015-11-22T00:00:00Z |
| WEEKLY 2015-48 | 2015-11-23T00:00:00Z | 2015-11-29T00:00:00Z |
| WEEKLY 2015-49 | 2015-11-30T00:00:00Z | 2015-12-06T00:00:00Z |
| WEEKLY 2015-50 | 2015-12-07T00:00:00Z | 2015-12-13T00:00:00Z |
| WEEKLY 2015-51 | 2015-12-14T00:00:00Z | 2015-12-20T00:00:00Z |
| WEEKLY 2015-52 | 2015-12-21T00:00:00Z | 2015-12-27T00:00:00Z |
| WEEKLY 2015-53 | 2015-12-28T00:00:00Z | 2015-12-31T00:00:00Z |
| WEEKLY 2015-53 | 2016-01-01T00:00:00Z | 2016-01-03T00:00:00Z |
| WEEKLY 2016-01 | 2016-01-04T00:00:00Z | 2016-01-10T00:00:00Z |
| WEEKLY 2016-02 | 2016-01-11T00:00:00Z | 2016-01-17T00:00:00Z |
| WEEKLY 2016-03 | 2016-01-18T00:00:00Z | 2016-01-24T00:00:00Z |
| WEEKLY 2016-04 | 2016-01-25T00:00:00Z | 2016-01-31T00:00:00Z |
| WEEKLY 2016-05 | 2016-02-01T00:00:00Z | 2016-02-07T00:00:00Z |
| WEEKLY 2016-06 | 2016-02-08T00:00:00Z | 2016-02-14T00:00:00Z |
| WEEKLY 2016-07 | 2016-02-15T00:00:00Z | 2016-02-21T00:00:00Z |
| WEEKLY 2016-08 | 2016-02-22T00:00:00Z | 2016-02-28T00:00:00Z |
| WEEKLY 2016-09 | 2016-02-29T00:00:00Z | 2016-03-06T00:00:00Z |
| WEEKLY 2016-10 | 2016-03-07T00:00:00Z | 2016-03-13T00:00:00Z |
| WEEKLY 2016-11 | 2016-03-14T00:00:00Z | 2016-03-20T00:00:00Z |
| WEEKLY 2016-12 | 2016-03-21T00:00:00Z | 2016-03-27T00:00:00Z |
| WEEKLY 2016-13 | 2016-03-28T00:00:00Z | 2016-04-03T00:00:00Z |
| WEEKLY 2016-14 | 2016-04-04T00:00:00Z | 2016-04-10T00:00:00Z |
| WEEKLY 2016-15 | 2016-04-11T00:00:00Z | 2016-04-17T00:00:00Z |
| WEEKLY 2016-16 | 2016-04-18T00:00:00Z | 2016-04-24T00:00:00Z |
| WEEKLY 2016-17 | 2016-04-25T00:00:00Z | 2016-05-01T00:00:00Z |
| WEEKLY 2016-18 | 2016-05-02T00:00:00Z | 2016-05-08T00:00:00Z |
| WEEKLY 2016-19 | 2016-05-09T00:00:00Z | 2016-05-15T00:00:00Z |
| WEEKLY 2016-20 | 2016-05-16T00:00:00Z | 2016-05-22T00:00:00Z |
| WEEKLY 2016-21 | 2016-05-23T00:00:00Z | 2016-05-29T00:00:00Z |
| WEEKLY 2016-22 | 2016-05-30T00:00:00Z | 2016-06-05T00:00:00Z |
| WEEKLY 2016-23 | 2016-06-06T00:00:00Z | 2016-06-12T00:00:00Z |
| WEEKLY 2016-24 | 2016-06-13T00:00:00Z | 2016-06-19T00:00:00Z |
| WEEKLY 2016-25 | 2016-06-20T00:00:00Z | 2016-06-26T00:00:00Z |
| WEEKLY 2016-26 | 2016-06-27T00:00:00Z | 2016-07-03T00:00:00Z |
| WEEKLY 2016-27 | 2016-07-04T00:00:00Z | 2016-07-10T00:00:00Z |
| WEEKLY 2016-28 | 2016-07-11T00:00:00Z | 2016-07-17T00:00:00Z |
| WEEKLY 2016-29 | 2016-07-18T00:00:00Z | 2016-07-24T00:00:00Z |
| WEEKLY 2016-30 | 2016-07-25T00:00:00Z | 2016-07-31T00:00:00Z |
| WEEKLY 2016-31 | 2016-08-01T00:00:00Z | 2016-08-07T00:00:00Z |
| WEEKLY 2016-32 | 2016-08-08T00:00:00Z | 2016-08-14T00:00:00Z |
| WEEKLY 2016-33 | 2016-08-15T00:00:00Z | 2016-08-21T00:00:00Z |
| WEEKLY 2016-34 | 2016-08-22T00:00:00Z | 2016-08-28T00:00:00Z |
| WEEKLY 2016-35 | 2016-08-29T00:00:00Z | 2016-09-04T00:00:00Z |
| WEEKLY 2016-36 | 2016-09-05T00:00:00Z | 2016-09-11T00:00:00Z |
| WEEKLY 2016-37 | 2016-09-12T00:00:00Z | 2016-09-18T00:00:00Z |
| WEEKLY 2016-38 | 2016-09-19T00:00:00Z | 2016-09-25T00:00:00Z |
| WEEKLY 2016-39 | 2016-09-26T00:00:00Z | 2016-10-02T00:00:00Z |
| WEEKLY 2016-40 | 2016-10-03T00:00:00Z | 2016-10-09T00:00:00Z |
| WEEKLY 2016-41 | 2016-10-10T00:00:00Z | 2016-10-16T00:00:00Z |
| WEEKLY 2016-42 | 2016-10-17T00:00:00Z | 2016-10-23T00:00:00Z |
| WEEKLY 2016-43 | 2016-10-24T00:00:00Z | 2016-10-30T00:00:00Z |
| WEEKLY 2016-44 | 2016-10-31T00:00:00Z | 2016-11-06T00:00:00Z |
| WEEKLY 2016-45 | 2016-11-07T00:00:00Z | 2016-11-13T00:00:00Z |
| WEEKLY 2016-46 | 2016-11-14T00:00:00Z | 2016-11-20T00:00:00Z |
| WEEKLY 2016-47 | 2016-11-21T00:00:00Z | 2016-11-27T00:00:00Z |
| WEEKLY 2016-48 | 2016-11-28T00:00:00Z | 2016-12-04T00:00:00Z |
| WEEKLY 2016-49 | 2016-12-05T00:00:00Z | 2016-12-11T00:00:00Z |
| WEEKLY 2016-50 | 2016-12-12T00:00:00Z | 2016-12-18T00:00:00Z |
| WEEKLY 2016-51 | 2016-12-19T00:00:00Z | 2016-12-25T00:00:00Z |
| WEEKLY 2016-52 | 2016-12-26T00:00:00Z | 2016-12-31T00:00:00Z |
| WEEKLY 2016-52 | 2017-01-01T00:00:00Z | 2017-01-01T00:00:00Z |
| WEEKLY 2017-01 | 2017-01-02T00:00:00Z | 2017-01-08T00:00:00Z |
| WEEKLY 2017-02 | 2017-01-09T00:00:00Z | 2017-01-15T00:00:00Z |
| WEEKLY 2017-03 | 2017-01-16T00:00:00Z | 2017-01-22T00:00:00Z |
| WEEKLY 2017-04 | 2017-01-23T00:00:00Z | 2017-01-29T00:00:00Z |
| WEEKLY 2017-05 | 2017-01-30T00:00:00Z | 2017-02-05T00:00:00Z |
| WEEKLY 2017-06 | 2017-02-06T00:00:00Z | 2017-02-12T00:00:00Z |
| WEEKLY 2017-07 | 2017-02-13T00:00:00Z | 2017-02-19T00:00:00Z |
| WEEKLY 2017-08 | 2017-02-20T00:00:00Z | 2017-02-26T00:00:00Z |
| WEEKLY 2017-09 | 2017-02-27T00:00:00Z | 2017-03-05T00:00:00Z |
| WEEKLY 2017-10 | 2017-03-06T00:00:00Z | 2017-03-12T00:00:00Z |
| WEEKLY 2017-11 | 2017-03-13T00:00:00Z | 2017-03-19T00:00:00Z |
| WEEKLY 2017-12 | 2017-03-20T00:00:00Z | 2017-03-26T00:00:00Z |
| WEEKLY 2017-13 | 2017-03-27T00:00:00Z | 2017-04-02T00:00:00Z |
| WEEKLY 2017-14 | 2017-04-03T00:00:00Z | 2017-04-09T00:00:00Z |
| WEEKLY 2017-15 | 2017-04-10T00:00:00Z | 2017-04-16T00:00:00Z |
| WEEKLY 2017-16 | 2017-04-17T00:00:00Z | 2017-04-23T00:00:00Z |
| WEEKLY 2017-17 | 2017-04-24T00:00:00Z | 2017-04-30T00:00:00Z |
| WEEKLY 2017-18 | 2017-05-01T00:00:00Z | 2017-05-07T00:00:00Z |
| WEEKLY 2017-19 | 2017-05-08T00:00:00Z | 2017-05-14T00:00:00Z |
| WEEKLY 2017-20 | 2017-05-15T00:00:00Z | 2017-05-21T00:00:00Z |
| WEEKLY 2017-21 | 2017-05-22T00:00:00Z | 2017-05-28T00:00:00Z |
| WEEKLY 2017-22 | 2017-05-29T00:00:00Z | 2017-06-04T00:00:00Z |
| WEEKLY 2017-23 | 2017-06-05T00:00:00Z | 2017-06-11T00:00:00Z |
| WEEKLY 2017-24 | 2017-06-12T00:00:00Z | 2017-06-18T00:00:00Z |
| WEEKLY 2017-25 | 2017-06-19T00:00:00Z | 2017-06-25T00:00:00Z |
| WEEKLY 2017-26 | 2017-06-26T00:00:00Z | 2017-07-02T00:00:00Z |
| WEEKLY 2017-27 | 2017-07-03T00:00:00Z | 2017-07-09T00:00:00Z |
| WEEKLY 2017-28 | 2017-07-10T00:00:00Z | 2017-07-16T00:00:00Z |
| WEEKLY 2017-29 | 2017-07-17T00:00:00Z | 2017-07-23T00:00:00Z |
| WEEKLY 2017-30 | 2017-07-24T00:00:00Z | 2017-07-30T00:00:00Z |
| WEEKLY 2017-31 | 2017-07-31T00:00:00Z | 2017-08-06T00:00:00Z |
| WEEKLY 2017-32 | 2017-08-07T00:00:00Z | 2017-08-13T00:00:00Z |
| WEEKLY 2017-33 | 2017-08-14T00:00:00Z | 2017-08-20T00:00:00Z |
| WEEKLY 2017-34 | 2017-08-21T00:00:00Z | 2017-08-27T00:00:00Z |
| WEEKLY 2017-35 | 2017-08-28T00:00:00Z | 2017-09-03T00:00:00Z |
| WEEKLY 2017-36 | 2017-09-04T00:00:00Z | 2017-09-10T00:00:00Z |
| WEEKLY 2017-37 | 2017-09-11T00:00:00Z | 2017-09-17T00:00:00Z |
| WEEKLY 2017-38 | 2017-09-18T00:00:00Z | 2017-09-24T00:00:00Z |
| WEEKLY 2017-39 | 2017-09-25T00:00:00Z | 2017-10-01T00:00:00Z |
| WEEKLY 2017-40 | 2017-10-02T00:00:00Z | 2017-10-08T00:00:00Z |
| WEEKLY 2017-41 | 2017-10-09T00:00:00Z | 2017-10-15T00:00:00Z |
| WEEKLY 2017-42 | 2017-10-16T00:00:00Z | 2017-10-22T00:00:00Z |
| WEEKLY 2017-43 | 2017-10-23T00:00:00Z | 2017-10-29T00:00:00Z |
| WEEKLY 2017-44 | 2017-10-30T00:00:00Z | 2017-11-05T00:00:00Z |
| WEEKLY 2017-45 | 2017-11-06T00:00:00Z | 2017-11-12T00:00:00Z |
| WEEKLY 2017-46 | 2017-11-13T00:00:00Z | 2017-11-19T00:00:00Z |
| WEEKLY 2017-47 | 2017-11-20T00:00:00Z | 2017-11-26T00:00:00Z |
| WEEKLY 2017-48 | 2017-11-27T00:00:00Z | 2017-12-03T00:00:00Z |
| WEEKLY 2017-49 | 2017-12-04T00:00:00Z | 2017-12-10T00:00:00Z |
| WEEKLY 2017-50 | 2017-12-11T00:00:00Z | 2017-12-17T00:00:00Z |
| WEEKLY 2017-51 | 2017-12-18T00:00:00Z | 2017-12-24T00:00:00Z |
| WEEKLY 2017-52 | 2017-12-25T00:00:00Z | 2017-12-31T00:00:00Z |
| WEEKLY 2018-01 | 2018-01-01T00:00:00Z | 2018-01-07T00:00:00Z |
| WEEKLY 2018-02 | 2018-01-08T00:00:00Z | 2018-01-14T00:00:00Z |
| WEEKLY 2018-03 | 2018-01-15T00:00:00Z | 2018-01-21T00:00:00Z |
| WEEKLY 2018-04 | 2018-01-22T00:00:00Z | 2018-01-28T00:00:00Z |
| WEEKLY 2018-05 | 2018-01-29T00:00:00Z | 2018-02-04T00:00:00Z |
| WEEKLY 2018-06 | 2018-02-05T00:00:00Z | 2018-02-11T00:00:00Z |
| WEEKLY 2018-07 | 2018-02-12T00:00:00Z | 2018-02-18T00:00:00Z |
| WEEKLY 2018-08 | 2018-02-19T00:00:00Z | 2018-02-25T00:00:00Z |
| WEEKLY 2018-09 | 2018-02-26T00:00:00Z | 2018-03-04T00:00:00Z |
| WEEKLY 2018-10 | 2018-03-05T00:00:00Z | 2018-03-11T00:00:00Z |
| WEEKLY 2018-11 | 2018-03-12T00:00:00Z | 2018-03-18T00:00:00Z |
| WEEKLY 2018-12 | 2018-03-19T00:00:00Z | 2018-03-25T00:00:00Z |
| WEEKLY 2018-13 | 2018-03-26T00:00:00Z | 2018-04-01T00:00:00Z |
| WEEKLY 2018-14 | 2018-04-02T00:00:00Z | 2018-04-08T00:00:00Z |
| WEEKLY 2018-15 | 2018-04-09T00:00:00Z | 2018-04-15T00:00:00Z |
| WEEKLY 2018-16 | 2018-04-16T00:00:00Z | 2018-04-22T00:00:00Z |
| WEEKLY 2018-17 | 2018-04-23T00:00:00Z | 2018-04-29T00:00:00Z |
| WEEKLY 2018-18 | 2018-04-30T00:00:00Z | 2018-05-06T00:00:00Z |
| WEEKLY 2018-19 | 2018-05-07T00:00:00Z | 2018-05-13T00:00:00Z |
| WEEKLY 2018-20 | 2018-05-14T00:00:00Z | 2018-05-20T00:00:00Z |
| WEEKLY 2018-21 | 2018-05-21T00:00:00Z | 2018-05-27T00:00:00Z |
| WEEKLY 2018-22 | 2018-05-28T00:00:00Z | 2018-06-03T00:00:00Z |
| WEEKLY 2018-23 | 2018-06-04T00:00:00Z | 2018-06-10T00:00:00Z |
| WEEKLY 2018-24 | 2018-06-11T00:00:00Z | 2018-06-17T00:00:00Z |
| WEEKLY 2018-25 | 2018-06-18T00:00:00Z | 2018-06-24T00:00:00Z |
| WEEKLY 2018-26 | 2018-06-25T00:00:00Z | 2018-07-01T00:00:00Z |
| WEEKLY 2018-27 | 2018-07-02T00:00:00Z | 2018-07-08T00:00:00Z |
| WEEKLY 2018-28 | 2018-07-09T00:00:00Z | 2018-07-15T00:00:00Z |
| WEEKLY 2018-29 | 2018-07-16T00:00:00Z | 2018-07-22T00:00:00Z |
| WEEKLY 2018-30 | 2018-07-23T00:00:00Z | 2018-07-29T00:00:00Z |
| WEEKLY 2018-31 | 2018-07-30T00:00:00Z | 2018-08-05T00:00:00Z |
| WEEKLY 2018-32 | 2018-08-06T00:00:00Z | 2018-08-12T00:00:00Z |
| WEEKLY 2018-33 | 2018-08-13T00:00:00Z | 2018-08-19T00:00:00Z |
| WEEKLY 2018-34 | 2018-08-20T00:00:00Z | 2018-08-26T00:00:00Z |
| WEEKLY 2018-35 | 2018-08-27T00:00:00Z | 2018-09-02T00:00:00Z |
| WEEKLY 2018-36 | 2018-09-03T00:00:00Z | 2018-09-09T00:00:00Z |
| WEEKLY 2018-37 | 2018-09-10T00:00:00Z | 2018-09-16T00:00:00Z |
| WEEKLY 2018-38 | 2018-09-17T00:00:00Z | 2018-09-23T00:00:00Z |
| WEEKLY 2018-39 | 2018-09-24T00:00:00Z | 2018-09-30T00:00:00Z |
| WEEKLY 2018-40 | 2018-10-01T00:00:00Z | 2018-10-07T00:00:00Z |
| WEEKLY 2018-41 | 2018-10-08T00:00:00Z | 2018-10-14T00:00:00Z |
| WEEKLY 2018-42 | 2018-10-15T00:00:00Z | 2018-10-21T00:00:00Z |
| WEEKLY 2018-43 | 2018-10-22T00:00:00Z | 2018-10-28T00:00:00Z |
| WEEKLY 2018-44 | 2018-10-29T00:00:00Z | 2018-11-04T00:00:00Z |
| WEEKLY 2018-45 | 2018-11-05T00:00:00Z | 2018-11-11T00:00:00Z |
| WEEKLY 2018-46 | 2018-11-12T00:00:00Z | 2018-11-18T00:00:00Z |
| WEEKLY 2018-47 | 2018-11-19T00:00:00Z | 2018-11-25T00:00:00Z |
| WEEKLY 2018-48 | 2018-11-26T00:00:00Z | 2018-12-02T00:00:00Z |
| WEEKLY 2018-49 | 2018-12-03T00:00:00Z | 2018-12-09T00:00:00Z |
| WEEKLY 2018-50 | 2018-12-10T00:00:00Z | 2018-12-16T00:00:00Z |
| WEEKLY 2018-51 | 2018-12-17T00:00:00Z | 2018-12-23T00:00:00Z |
| WEEKLY 2018-52 | 2018-12-24T00:00:00Z | 2018-12-30T00:00:00Z |
| WEEKLY 2019-01 | 2018-12-31T00:00:00Z | 2018-12-31T00:00:00Z |
| WEEKLY 2019-01 | 2019-01-01T00:00:00Z | 2019-01-06T00:00:00Z |
| WEEKLY 2019-02 | 2019-01-07T00:00:00Z | 2019-01-13T00:00:00Z |
| WEEKLY 2019-03 | 2019-01-14T00:00:00Z | 2019-01-20T00:00:00Z |
| WEEKLY 2019-04 | 2019-01-21T00:00:00Z | 2019-01-27T00:00:00Z |
| WEEKLY 2019-05 | 2019-01-28T00:00:00Z | 2019-02-03T00:00:00Z |
| WEEKLY 2019-06 | 2019-02-04T00:00:00Z | 2019-02-10T00:00:00Z |
| WEEKLY 2019-07 | 2019-02-11T00:00:00Z | 2019-02-17T00:00:00Z |
| WEEKLY 2019-08 | 2019-02-18T00:00:00Z | 2019-02-24T00:00:00Z |
| WEEKLY 2019-09 | 2019-02-25T00:00:00Z | 2019-03-03T00:00:00Z |
| WEEKLY 2019-10 | 2019-03-04T00:00:00Z | 2019-03-10T00:00:00Z |
| WEEKLY 2019-11 | 2019-03-11T00:00:00Z | 2019-03-17T00:00:00Z |
| WEEKLY 2019-12 | 2019-03-18T00:00:00Z | 2019-03-24T00:00:00Z |
| WEEKLY 2019-13 | 2019-03-25T00:00:00Z | 2019-03-31T00:00:00Z |
| WEEKLY 2019-14 | 2019-04-01T00:00:00Z | 2019-04-07T00:00:00Z |
| WEEKLY 2019-15 | 2019-04-08T00:00:00Z | 2019-04-14T00:00:00Z |
| WEEKLY 2019-16 | 2019-04-15T00:00:00Z | 2019-04-21T00:00:00Z |
| WEEKLY 2019-17 | 2019-04-22T00:00:00Z | 2019-04-28T00:00:00Z |
| WEEKLY 2019-18 | 2019-04-29T00:00:00Z | 2019-05-05T00:00:00Z |
| WEEKLY 2019-19 | 2019-05-06T00:00:00Z | 2019-05-12T00:00:00Z |
| WEEKLY 2019-20 | 2019-05-13T00:00:00Z | 2019-05-19T00:00:00Z |
| WEEKLY 2019-21 | 2019-05-20T00:00:00Z | 2019-05-26T00:00:00Z |
| WEEKLY 2019-22 | 2019-05-27T00:00:00Z | 2019-06-02T00:00:00Z |
| WEEKLY 2019-23 | 2019-06-03T00:00:00Z | 2019-06-09T00:00:00Z |
| WEEKLY 2019-24 | 2019-06-10T00:00:00Z | 2019-06-16T00:00:00Z |
| WEEKLY 2019-25 | 2019-06-17T00:00:00Z | 2019-06-23T00:00:00Z |
| WEEKLY 2019-26 | 2019-06-24T00:00:00Z | 2019-06-30T00:00:00Z |
| WEEKLY 2019-27 | 2019-07-01T00:00:00Z | 2019-07-07T00:00:00Z |
| WEEKLY 2019-28 | 2019-07-08T00:00:00Z | 2019-07-14T00:00:00Z |
| WEEKLY 2019-29 | 2019-07-15T00:00:00Z | 2019-07-21T00:00:00Z |
| WEEKLY 2019-30 | 2019-07-22T00:00:00Z | 2019-07-28T00:00:00Z |
| WEEKLY 2019-31 | 2019-07-29T00:00:00Z | 2019-08-04T00:00:00Z |
| WEEKLY 2019-32 | 2019-08-05T00:00:00Z | 2019-08-11T00:00:00Z |
| WEEKLY 2019-33 | 2019-08-12T00:00:00Z | 2019-08-18T00:00:00Z |
| WEEKLY 2019-34 | 2019-08-19T00:00:00Z | 2019-08-25T00:00:00Z |
| WEEKLY 2019-35 | 2019-08-26T00:00:00Z | 2019-09-01T00:00:00Z |
| WEEKLY 2019-36 | 2019-09-02T00:00:00Z | 2019-09-08T00:00:00Z |
| WEEKLY 2019-37 | 2019-09-09T00:00:00Z | 2019-09-15T00:00:00Z |
| WEEKLY 2019-38 | 2019-09-16T00:00:00Z | 2019-09-22T00:00:00Z |
| WEEKLY 2019-39 | 2019-09-23T00:00:00Z | 2019-09-29T00:00:00Z |
| WEEKLY 2019-40 | 2019-09-30T00:00:00Z | 2019-10-06T00:00:00Z |
| WEEKLY 2019-41 | 2019-10-07T00:00:00Z | 2019-10-13T00:00:00Z |
| WEEKLY 2019-42 | 2019-10-14T00:00:00Z | 2019-10-20T00:00:00Z |
| WEEKLY 2019-43 | 2019-10-21T00:00:00Z | 2019-10-27T00:00:00Z |
| WEEKLY 2019-44 | 2019-10-28T00:00:00Z | 2019-11-03T00:00:00Z |
| WEEKLY 2019-45 | 2019-11-04T00:00:00Z | 2019-11-10T00:00:00Z |
| WEEKLY 2019-46 | 2019-11-11T00:00:00Z | 2019-11-17T00:00:00Z |
| WEEKLY 2019-47 | 2019-11-18T00:00:00Z | 2019-11-24T00:00:00Z |
| WEEKLY 2019-48 | 2019-11-25T00:00:00Z | 2019-12-01T00:00:00Z |
| WEEKLY 2019-49 | 2019-12-02T00:00:00Z | 2019-12-08T00:00:00Z |
| WEEKLY 2019-50 | 2019-12-09T00:00:00Z | 2019-12-15T00:00:00Z |
| WEEKLY 2019-51 | 2019-12-16T00:00:00Z | 2019-12-22T00:00:00Z |
| WEEKLY 2019-52 | 2019-12-23T00:00:00Z | 2019-12-29T00:00:00Z |
| WEEKLY 2020-01 | 2019-12-30T00:00:00Z | 2019-12-31T00:00:00Z |

If you just want a list of all the ISO weeks containing dates 2014-01-01 through 2019-12-31 then:

SELECT 'WEEKLY ' || TO_CHAR( dt, 'IYYY-IW' ) AS DESCR,
       dt AS StartDT,
       dt + INTERVAL '6' DAY
FROM   (
  SELECT TRUNC( DATE '2014-01-01' + ( LEVEL - 1 ) * INTERVAL '7' DAY, 'IW' ) AS dt
  FROM   DUAL
  CONNECT BY TRUNC( DATE '2014-01-01' + ( LEVEL - 1 ) * INTERVAL '7' DAY, 'IW' ) < DATE '2020-01-01'
);

This works great! Thanks. If I want the week to cross the year boundary, how would I make that work?
@baskinsr See my update at the bottom of the answer (posted just before your comment).

Littlefoot · Accepted Answer · 2018-04-06 20:20:14Z

0

You're starting at 01 Jan 2014, add 365 days, so - why do you expect it to go up to 2020? Change "365" to something else. That "something" shouldn't be "365 * 7" nor should you consider leap years - simply subtract two dates (the result is number of days). Something like this (the inline view is what you need):

SQL> select min(dat), max(dat), count(*)
  2  from
  3  (
  4  select date '2014-01-01' + level - 1 dat
  5  from dual
  6  connect by level <= date '2020-12-31' - date '2014-01-01' + 1
  7  );

MIN(DAT)   MAX(DAT)     COUNT(*)
---------- ---------- ----------
01.01.2014 31.12.2020       2557

[EDIT]

Aha; now I understand. It is the year that confuses you, because all those years share the same week numbers, so - when you performed GROUP BY week only, you got ... well, what you got. You'll have to GROUP BY year as well, such as

with all_dates as
  (select date '2014-01-01' + level - 1 dat
   from dual
   connect by level <= date '2020-12-31' - date '2014-01-01' + 1
  ),
periods as
  (select dat, to_char(dat, 'IW') period, extract(year from dat) yr
   from all_dates
  )
select min(dat) startdate,
       min(dat) + 6 enddate,
       period,
       yr
from periods
group by period, yr
order by yr, period;

07.12.2020 13.12.2020 50       2020
14.12.2020 20.12.2020 51       2020
21.12.2020 27.12.2020 52       2020
28.12.2020 03.01.2021 53       2020

367 rows selected.

SQL>

edited Apr 6, 2018 at 20:20

answered Apr 6, 2018 at 19:41

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

3 Comments

baskinsr Over a year ago

What happens when I choose anything larger than 365 is I get 53 records instead of the standard 52 with the 53rd record having a start date of '12/28/2015' and end date of '1/3/2016'. It doesn't just continue creating weeks until I reach that date or that number of days

baskinsr Over a year ago

That got me a lot closer! I was able to get the monthly calendar to work perfectly. The weekly calendar, however, I have issues at the end of years, it doesn't cross over years properly for a week to start in one year and end in another year

Littlefoot Over a year ago

Was it because I forgot to add days to "enddate"? If so, I modified it now. Better?

Collectives™ on Stack Overflow

Create Dates Oracle

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related