CODEWITHSUNDEEP
sqloracle-database
Harshil Shah
Harshil Shah

Reputation: 21

How do I convert a Week Number to From date of the week in oracle

Suppose I enter WeekNo: 14 the query should return the From Date: April 4th 2016, since the week 14 starts from 4th April to 10th April

select to_date('14','iw') FROM dual;

Upvotes: 0

Views: 6241

Answers (4)

Анатолий Предеин
Анатолий Предеин

Reputation: 571

something like this ? (it work for current year) there discard data from another years

with dates as (select to_char(
                        to_date('1.01.'||extract(year from sysdate),'dd.mm.yyyy'  ) + level -1 
                        ,'IW') we,
                        to_date('1.01.'||extract(year from sysdate),'dd.mm.yyyy'  ) + level -1 da
                 from dual 
                connect by level <= 365 + 10 )
select * from (
select case 
         when  -- we = null if number of week in jan > 1,2,3,4....
               ((to_number(we) > 40 )
               and extract(year from sysdate) = extract(year from da)
               and extract(month from da) = '01') or
               -- we = null when current year < year of da
               (extract(year from sysdate) != extract(year from da))
                then
                  null 
            else we
      end we,
      da
  from dates
)  
where we = 14
  and rownum = 1

Upvotes: 2

user5683823
user5683823

Reputation:

Here is a simple and direct computation, taking advantage of various Oracle date functions. Since it compares to what Oracle already counts as ISO week etc., it shouldn't be subject to any of the difficulties other solutions correctly point to and address with additional code.

The "magic number" 14 in the formula should instead be a bind variable, perhaps :iw, or some other mechanism of inputting the ISO week number into the query.

select trunc(sysdate, 'iw') - 7 * (to_number(to_char(trunc(sysdate), 'iw')) - 14) as dt 
from   dual;

DT
----------
2016-04-04

1 row selected.

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

Dealing with ISO-Weeks is not trivial, for example January, 1st 2016 is week 53 of 2015, see

select to_char(date '2016-01-01', 'iyyy-"W"iw') from dual;

So, providing only the week number without the (ISO-) year is ambiguous - although it is obvious as along as you are not around new-years date.

Some time ago I wrote this function to get the date from ISO-Week.

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;

Of course you can just select NEXT_DAY(TO_DATE( YEAR || '0104', 'YYYYMMDD' ) - 7, 'MONDAY') + ( week - 1 ) * 7;, however this would not be error-safe if somebody uses the wrong year.

Upvotes: 1

Thomas G
Thomas G

Reputation: 10226

If it is not an issue to append the year to the week you are looking for, you can also use this :

SELECT (TRUNC ( TO_DATE (SUBSTR ('201627', 1, 4) || '0131', 'YYYY'|| 'MMDD'), 'IYYY') 
    + ( 7 * ( TO_NUMBER (SUBSTR ('201627', 5)) - 1)) ) AS iw_Monday
FROM dual

With in this example 201627 being the YYYYIW you are looking for. It will return the date of the MONDAY of that week.

Found it on Oracle forums, there are a couple of other solutions there. I found this one to be the most elegant.

The advantage is that you do everything from the SELECT, and you don't need either a function or PL/SQL or a WHERE clause.

Disadvantages : you must append the year and specify your search week 2 times, unless you use a variable

Upvotes: 0

Related Questions