I have borrowed a query suggestion from the approved answer on the following Stack question to calculate the date ranges of a given IW week number. After incorporating some of the syntax into my own query I created the following SQL:
SELECT
t.termination_date,
to_char(t.termination_date,'IW') IW_WEEK,
next_day(to_date( to_char(t.termination_date,'IW') *7, 'DDD')-8, 'mon') WEEK_START_DATE,
next_day(to_date( to_char(t.termination_date,'IW') *7, 'DDD'), 'sun') WEEK_END_DATE
FROM
table t
What I am noticing is, that these week ranges are only returning week ranges for 2023, even though the termination_date is in a different year. I am not sure if this is caused by the design of the next_day() function or it’s some sort of built-in behavior to convert a IW Week into the current year, but I haven’t been able to find information to support this.
What I would like to do is take a given date (termination date in my example) and calculate the date range of an IW week number in the year in which the date occurs.
You seem to just want to use TRUNC(date_column , 'IW') to get the start of the week (Monday) and then add 6 days to get the Sunday.
If you want to calculate the start and end date for the week from the ISO year and ISO week then start from 4th January of the ISO year (as that is always in the first ISO week) and then truncate to the start of the ISO week and add multiples of 7 days to get to the correct ISO week:
SELECT termination_date,
to_char(termination_date,'IW') AS IW_WEEK,
TRUNC(termination_date,'IW') AS WEEK_START_DATE,
TRUNC(termination_date,'IW') + INTERVAL '6' DAY AS WEEK_END_DATE,
TRUNC(TO_DATE(TO_CHAR(termination_date, 'IYYY') || '-01-04', 'YYYY-MM-DD'), 'IW')
+ INTERVAL '7' DAY * (TO_CHAR(termination_date, 'IW') - 1) AS week_start_date2,
TRUNC(TO_DATE(TO_CHAR(termination_date, 'IYYY') || '-01-04', 'YYYY-MM-DD'), 'IW')
+ INTERVAL '7' DAY * TO_CHAR(termination_date, 'IW') - 1 AS week_end_date2
FROM table_name
Which, for the sample data:
CREATE TABLE table_name (termination_date) AS
SELECT DATE '2022-01-06' + LEVEL FROM DUAL CONNECT BY LEVEL <= 15;
Outputs:
| TERMINATION_DATE | IW_WEEK | WEEK_START_DATE | WEEK_END_DATE | WEEK_START_DATE2 | WEEK_END_DATE2 |
|---|---|---|---|---|---|
| 2022-01-07 00:00:00 | 01 | 2022-01-03 00:00:00 | 2022-01-09 00:00:00 | 2022-01-03 00:00:00 | 2022-01-09 00:00:00 |
| 2022-01-08 00:00:00 | 01 | 2022-01-03 00:00:00 | 2022-01-09 00:00:00 | 2022-01-03 00:00:00 | 2022-01-09 00:00:00 |
| 2022-01-09 00:00:00 | 01 | 2022-01-03 00:00:00 | 2022-01-09 00:00:00 | 2022-01-03 00:00:00 | 2022-01-09 00:00:00 |
| 2022-01-10 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-11 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-12 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-13 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-14 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-15 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-16 00:00:00 | 02 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 | 2022-01-10 00:00:00 | 2022-01-16 00:00:00 |
| 2022-01-17 00:00:00 | 03 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 |
| 2022-01-18 00:00:00 | 03 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 |
| 2022-01-19 00:00:00 | 03 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 |
| 2022-01-20 00:00:00 | 03 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 |
| 2022-01-21 00:00:00 | 03 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 | 2022-01-17 00:00:00 | 2022-01-23 00:00:00 |