How to convert week number to date range in Oracle?

Question

In Oracle we get week number from following query:

select to_char(TO_DATE(SYSDATE,'DD-MM-YY'),'IW') from dual

I want to get date range of given week number,
for example for week no:1 date range is 01-01-2017 to 08-01-2017.

is there any way to get the date range for given week number?

maybe this question can help you: https://stackoverflow.com/questions/38180947/how-do-i-convert-a-week-number-to-from-date-of-the-week-in-oracle — Cyrille MODIANO, Sep 27 '17 at 07:37
**NEVER** use `to_date` on something that is already a DATE. You cause an extra implicit conversion to take place that relies on your default NLS_DATE_FORMAT parameter - e.g. your query becomes `select to_char(to_date(to_char(sysdate, ''), 'DD-MM-YY'), 'IW') from dual`, and if your NLS_DATE_FORMAT isn't set to the same as your specified format, you're most likely going to get errors. And why are you using a two digit year anyway? Years have 4 digits!. Your query should be: `select to_char(sysdate, 'iw') from dual` — Boneist, Sep 27 '17 at 08:00
What about the year? Bear in mind, for example 2017-01-01 was week **2016W52**. So which date do you expect for week **52** (without specify the year)? Even your "current year" it could be 2016-12-26 or 2017-12-25 — Wernfried Domscheit, Sep 27 '17 at 08:48

score 8 · Accepted Answer · answered Sep 27 '17 at 07:56

"week no:1 date range is 01-01-2017 to 08-01-2017"

No it isn't. You're confusing 'IW' (which runs MON - SUN) with 'WW' which runs from the first day of the year:

SQL> with dts as (
  2       select date '2017-01-01' + (level-1) as dt
  3       from dual
  4       connect by level <= 8
  5  )
  6  select dt
  7         , to_char(dt, 'DY') as dy_dt
  8         , to_char(dt, 'IW') as iw_dt
  9         , to_char(dt, 'WW') as ww_dt
 10  from dts
 11  order by 1;

DT        DY_DT        IW WW
--------- ------------ -- --
01-JAN-17 SUN          52 01
02-JAN-17 MON          01 01
03-JAN-17 TUE          01 01
04-JAN-17 WED          01 01
05-JAN-17 THU          01 01
06-JAN-17 FRI          01 01
07-JAN-17 SAT          01 01
08-JAN-17 SUN          01 02

8 rows selected.

SQL>

However, it's easy enough to generate a range for the the IW week number. You need to multiple the IW number by 7 which you can convert to a date with the day of year mask. Then you can use next_day() function to get the previous Monday and the next Sunday relative to that date:

SQL> with tgt as (
  2      select to_date( &iw *7, 'DDD') as dt from dual
  3      )
  4  select next_day(dt-8, 'mon') as start_date
  5         , next_day(dt, 'sun') as end_date
  6* from tgt;
Enter value for iw: 23
old   2:     select to_date( &iw *7, 'DDD') as dt from dual
new   2:     select to_date( 23 *7, 'DDD') as dt from dual

START_DAT END_DATE
--------- ---------
05-JUN-17 11-JUN-17

SQL>

Obvious this solution uses my NLS Settings (English): you may need to tweak the solution if you use different settings.

To avoid nls dependence, you could do: `trunc(to_date(week_num*7, 'ddd'), 'iw')` to find the start of the week and then add 6 to that to find the end of the week — Boneist, Sep 27 '17 at 08:16
@Boneist - that formula is incorrect. Not sure if it works properly in other cases (I suspect not, but I may be wrong); but it will definitely fail when `week_num` is 53, because 53 + 7 = 371 and 'ddd' can never be 371; and leap weeks (num = 53) do exist - that's the whole point of the ISO week concept. — , Nov 07 '20 at 16:17

Ronnis · Answer 2 · 2017-09-27T11:07:25.497

These kinds of problems are easy to solve with calendar tables.

The following query builds on the assumption (ISO 8601) that the 4th of January is present in the first week in a year. Therefore I can generate a valid date in the first week of any year by constructing the 4th of January like: to_date(year || '-01-04', 'yyyy-mm-dd'). Oracle will tell me the day of week (sun=1, sat=7) for any date using to_char(date, 'D'). The 4th of JAN 2017 happens to be a wednesday (day 4). Subtracting 3 days will give me the first day (sunday) of the first week of the year. Now it is easy to find the start day in any given week in the year by simply adding 7 days for each week (not counting the first week).

with weeks as(
   select 2017 as year, 39 as week from dual union all
   select 2017 as year, 40 as week from dual union all
   select 2018 as year, 35 as week from dual
)
select a.*
      ,to_date(year || '-01-04', 'yyyy-mm-dd')     - to_number(to_char(to_date(year || '-01-04', 'yyyy-mm-dd'), 'D')) + 1 + (7 * (week-1)) as start_day
      ,to_date(year || '-01-04', 'yyyy-mm-dd') + 7 - to_number(to_char(to_date(year || '-01-04', 'yyyy-mm-dd'), 'D'))     + (7 * (week-1)) as end_day
 from weeks a;

Edit: These are the "convert" expressions you need to convert from week to date range. Note that 2017 and 39 are variable...

start date = to_date(2017 || '-01-04', 'yyyy-mm-dd')     - to_number(to_char(to_date(2017 || '-01-04', 'yyyy-mm-dd'), 'D')) + 1 + (7 * (39-1))
end date   = to_date(2017 || '-01-04', 'yyyy-mm-dd') + 7 - to_number(to_char(to_date(2017 || '-01-04', 'yyyy-mm-dd'), 'D'))     + (7 * (39-1))

Thanks for your answer, I have to provide all 52 weeks for this query to work right? — imsome1, Sep 27 '17 at 07:50
@imsome1, does the edit clarify your question? Don't need the WITH construct. I only provided it to be able to show you examples. I suppose your weeks will come from another table somewhere. — Ronnis, Sep 27 '17 at 11:08
Thanks for the explanation. That assumption really helped, because to_date() cannot process week numbers. — Max L, Nov 05 '21 at 22:15

Philippe Malera · Answer 3 · 2019-01-10T15:44:57.887

1

Here's a query to list all ISO weeks from 2001 to 2099

SELECT TO_CHAR(TRUNC(dt, 'IW') + 6, 'IYYY-IW') AS week, 
       TRUNC(dt, 'IW') AS start_date, 
       TRUNC(dt, 'IW') + 6 AS end_date
  FROM (SELECT DATE '2001-01-01' + ((LEVEL - 1) * 7) dt
          FROM DUAL
        CONNECT BY LEVEL <= 5165);

edited Jan 10 '19 at 15:44

answered Jan 10 '19 at 15:17

Philippe Malera

21
3

Two years ago to the current week: `SELECT TO_CHAR(TRUNC(dt, 'IW') + 6, 'IYYY-IW') AS week, TRUNC(dt, 'IW') AS start_date, TRUNC(dt, 'IW') + 6 AS end_date FROM ( SELECT sysdate-730 + ((LEVEL - 1) * 7) dt FROM DUAL CONNECT BY LEVEL <= 105 ) ORDER BY dt DESC;` Thank you @philippe-malera – tarcnux Oct 06 '21 at 16:28

Eduard Okhvat · Answer 4 · 2017-09-27T08:22:06.187

For the first and last week of year this query needs some CASE logic, but for other weeks works good. This solution use current NLS settings.

select to_char( start_of_week, 'day dd.mm.yyyy'  ) start_of_week,
       to_char( start_of_week + 6, 'day dd.mm.yyyy'  ) end_of_week
 from  
( 
select trunc( date '2017-01-01' + 38*7 , 'day')   start_of_week        
 from dual   
  )

1) date '2017-01-01' - in what year we look for weeks or it may be trunc (sysdate, 'YEAR') to take first day of current year

2) date '2017-01-01' + 38*7 - jump to 38th week

3) trunc ( ... , 'day' ) - gives date of first day of the week

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm

score 0 · Answer 5 · answered Sep 27 '17 at 08:56

I use this function:

FUNCTION ISOWeekDate(WEEK INTEGER, YEAR INTEGER) RETURN DATE DETERMINISTIC IS
    res DATE;
BEGIN
    IF WEEK > 53 OR WEEK < 1 THEN
        RAISE VALUE_ERROR;      
    END IF;
    res := NEXT_DAY(TO_DATE( YEAR || '0104', 'YYYYMMDD' ) - 7, 'MONDAY') + ( WEEK - 1 ) * 7;
    IF TO_CHAR(res, 'fmIYYY') = YEAR THEN
        RETURN res;
    ELSE
        RAISE VALUE_ERROR;
    END IF;
END ISOWeekDate;

Please note, according to my comment it is ambiguous if you only provide a week number without a year. The function returns the first day of given ISO Week.

How to convert week number to date range in Oracle?

5 Answers5