CODEWITHSUNDEEP
oracle-database
imsome1
imsome1

Reputation: 1192

How to convert week number to date range in Oracle?

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?

Upvotes: 0

Views: 15392

Answers (5)

Philippe Malera
Philippe Malera

Reputation: 21

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);

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

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))

Upvotes: 4

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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.

Upvotes: 0

Eduard Okhvat
Eduard Okhvat

Reputation: 206

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

Upvotes: 0

APC
APC

Reputation: 146219

"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.

Upvotes: 8

Related Questions