sql - Oracle query to return each week and month including mid week month changes - Stack Overflow

admin管理员组

文章数量:1345476

I'm trying to return all the individual weeks of the year, with the respective number of the week and number of the week respective to the month.

This query does that but how can I alter the logic to be that if a month ends and a new one begins that week is considered week 1 of the new month rather than week 4/5 of the current month ex: this current week started on 3/30/25 the query considers that week 5 or march whereas I need it to be week 1 on April

Heres the query I'm using FIDDLE

Also posted below, thanks for any assistance.

WITH RCTE (THE_DATE) AS (
  SELECT TRUNC(SYSDATE, 'IW')
  FROM DUAL
  UNION ALL
  SELECT THE_DATE + 7
  FROM RCTE
  WHERE THE_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1
)
SELECT THE_DATE,
  TO_CHAR(THE_DATE, 'YYYY') AS YYYY,
  TO_CHAR(THE_DATE, 'D') AS D,
  TO_CHAR(THE_DATE, 'W') AS W,
  TO_CHAR(THE_DATE, 'MM') AS MM,
  TO_CHAR(THE_DATE, 'IYYY') AS IYYY,
  TO_CHAR(THE_DATE, 'IW') AS IW
FROM RCTE
ORDER BY THE_DATE

I'm trying to return all the individual weeks of the year, with the respective number of the week and number of the week respective to the month.

This query does that but how can I alter the logic to be that if a month ends and a new one begins that week is considered week 1 of the new month rather than week 4/5 of the current month ex: this current week started on 3/30/25 the query considers that week 5 or march whereas I need it to be week 1 on April

Heres the query I'm using FIDDLE

Also posted below, thanks for any assistance.

WITH RCTE (THE_DATE) AS (
  SELECT TRUNC(SYSDATE, 'IW')
  FROM DUAL
  UNION ALL
  SELECT THE_DATE + 7
  FROM RCTE
  WHERE THE_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1
)
SELECT THE_DATE,
  TO_CHAR(THE_DATE, 'YYYY') AS YYYY,
  TO_CHAR(THE_DATE, 'D') AS D,
  TO_CHAR(THE_DATE, 'W') AS W,
  TO_CHAR(THE_DATE, 'MM') AS MM,
  TO_CHAR(THE_DATE, 'IYYY') AS IYYY,
  TO_CHAR(THE_DATE, 'IW') AS IW
FROM RCTE
ORDER BY THE_DATE

• sql
• oracle-database
• oracle11g

Share Improve this question Follow edited 10 hours ago Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked 15 hours ago donovan ricedonovan rice 3555 bronze badges 2

• Please edit the question and provide minimal reproducible example with the expected output for your query. – MT0 Commented 11 hours ago
• "if a month ends and a new one begins that week is considered week 1 of the new month rather than week 4/5 of the current month" Note: that is not the same logic as defined for ISO dates - with ISO dates the week is in a year, or month, if the majority of the week is in that year, or month (i.e. which year/month contains the middle day, Thursday, of the week). – MT0 Commented 11 hours ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

If the month has changed during the week then the Sunday of that week must be in the new month; therefore, you can find the start of the new month by:

• adding 6 days to the date to get to Sunday of that week;
• then truncating that date to the start of the month; then
• find truncate, again, to the start of the ISO week to take you back to the Monday of the week containing the first day of the month; finally
• to get the number of the week of the month, subtract the Monday of the ISO-week containing the start of the month from the original date and divide by 7 to get the number of weeks difference (and add 1 to get your expected value).

WITH RCTE (THE_DATE) AS (
  SELECT TRUNC(SYSDATE, 'IW')
  FROM   DUAL
UNION ALL
  SELECT THE_DATE + 7
  FROM   RCTE
  WHERE  THE_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1
)
SELECT THE_DATE,
       TO_CHAR(THE_DATE, 'YYYY') AS YYYY,
       TO_CHAR(THE_DATE, 'D') AS D,
       (THE_DATE - TRUNC(TRUNC(THE_DATE + INTERVAL '6' DAY, 'MM'), 'IW'))/7 + 1  AS W,
       TO_CHAR(THE_DATE + INTERVAL '6' DAY, 'MM') AS MM,
       TO_CHAR(THE_DATE, 'IYYY') AS IYYY,
       TO_CHAR(THE_DATE, 'IW') AS IW
FROM   RCTE
ORDER BY THE_DATE;

Which outputs:

THE_DATE	YYYY	D	W	MM	IYYY	IW
2025-03-31 00:00:00	2025	1	1	04	2025	14
2025-04-07 00:00:00	2025	1	2	04	2025	15
2025-04-14 00:00:00	2025	1	3	04	2025	16
2025-04-21 00:00:00	2025	1	4	04	2025	17
2025-04-28 00:00:00	2025	1	1	05	2025	18
2025-05-05 00:00:00	2025	1	2	05	2025	19
2025-05-12 00:00:00	2025	1	3	05	2025	20
2025-05-19 00:00:00	2025	1	4	05	2025	21
2025-05-26 00:00:00	2025	1	1	06	2025	22
2025-06-02 00:00:00	2025	1	2	06	2025	23
2025-06-09 00:00:00	2025	1	3	06	2025	24
2025-06-16 00:00:00	2025	1	4	06	2025	25
2025-06-23 00:00:00	2025	1	5	06	2025	26
2025-06-30 00:00:00	2025	1	1	07	2025	27
...	...	...	...	...	...	...

fiddle

Note: If you want to use the same logic as IYYY and IW for the week of month and month columns then don't add 6 days to check the Sunday, instead add 4 days to check which month the Thursday of that week falls into.

本文标签： sqlOracle query to return each week and month including mid week month changesStack Overflow