How to Convert YYYY-IW format to DD-MON-YY.?

this is right

select to_date(substr('2002-49',1,5)||to_char((to_number(substr('2002-49',6)))*7),'yyyy-ddd')-7 from dual

You can write any week in format 'yyyy-iw' instead of '2002-49'

good luck,
Kanan

Comments from gmyers and Kannan worked but not for all situations. For Example I ran the script for Week '2002-01'. I got the First Day of week as '01-Jan-02' and Last Day of week as '31-Dec-02'. This is not correct. The correct result is '31-Dec-01' is First Day and '06-Jan-02' is the last day.

I have changed the logic sent by gmyers a little bit to get correct result for any week of any year. This is a bit complex but it gives the correct result.

Thank you guys for giving me the startup.

Gopi.



select min_date, to_char(min_date,'DAY') DAY from
 (select to_date(substr(:Week_no,1,4)||'001'+rownum-1,'YYYYDDD') min_date
        from all_tables
  where rownum <= decode(mod(to_number(substr(:Week_no,1,4)),4),0,366,365)  -- To Handle Leap Year
  UNION
  -- To Fetch Dates of previous year falling into current year's 1st week
  select to_date(substr(:Week_no,1,4)-1||
         decode(mod(to_number(substr(:Week_no,1,4))-1,4),0,359,358)+rownum,'YYYYDDD') min_date
        from all_tables          
          where rownum <= 7
  UNION
  -- To Fetch Dates of Next year falling into current year's last week
  select to_date(substr(:Week_no,1,4)+1||'001'+rownum-1,'YYYYDDD') min_date
        from all_tables          
          where rownum <= 7                      
 )
where to_char(min_date,'IYYY-IW') = :Week_no

I run my code and get:
select to_date(substr('2002-01',1,5)||to_char((to_number(substr('2002-01',6)))*7),'yyyy-ddd')-7 first_day from dual
1 row(s) selected
      FIRST_DAY      
--------------------
         31-Dec-2001

select to_date(substr('2002-01',1,5)||to_char((to_number(substr('2002-01',6)))*7),'yyyy-ddd')-1 last_day from dual
1 row(s) selected
      LAST_DAY      
--------------------
          6-Jan-2002

good luck

Hi Kannan,

I ran your code for '2002-01' it did give correct answer, but not for '2003-01'

You code gave 12/31/02 as First Day and 01/06/03 as Last day. This is not the correct answer.

The correct values for '2003-01' are

First Date      Day Name
----------      --------
12/30/02        Monday

Last Date       Day Name
---------       --------
01/05/03        Sunday

I run your code for '2001-01' it did not give the correct result.


Thank you

Gopi.

try this:
select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),'yyyy-ddd'),'d')-6 first_day from dual
1 row(s) selected
      FIRST_DAY      
--------------------
         30-Dec-2002

select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),'yyyy-ddd'),'d') last_day from dual
1 row(s) selected
      LAST_DAY      
--------------------
          5-Jan-2003

select trunc(to_date(substr('2002-01',1,5)||to_char((to_number(substr('2002-01',6)))*7),'yyyy-ddd'),'d')-6 first_day from dual
1 row(s) selected
      FIRST_DAY      
--------------------
         31-Dec-2001

select trunc(to_date(substr('2002-01',1,5)||to_char((to_number(substr('2002-01',6)))*7),'yyyy-ddd'),'d') last_day from dual
1 row(s) selected
      LAST_DAY      
--------------------
          6-Jan-2002

good luck,
Kanan.

Your Query worked for most of the cases, but it failed in one case.

For Example I ran it for week 2000-52. It gave First day as 18-Dec-2000 and Last Day as 24-Dec-2000. This is correct.

Next I ran for week 2001-01, the results are 1-Jan-2001 and 7-Jan-2001. This is correct.

What happened to the days between 25-Dec-2000 and 31-Dec-2000, One whole week is missing. Actually there are 53 weeks in 2000. If I run for week 2002-53, it fails.

Thank you

Gopi.

try this
SELECT TRUNC(DECODE(ww, 53, TO_DATE(yy || '3112', 'yyyyddmm'), TO_DATE(yy || '-' || TO_CHAR(ww * 7), 'yyyy-ddd')), 'd') - 6 first_day
  FROM (SELECT SUBSTR('2000-53', 1, 4) yy, TO_NUMBER(SUBSTR('2000-53', 6)) ww
          FROM DUAL)
1 row(s) selected
       FIRST_DAY        
-----------------------
            25-Dec-2000

SELECT TRUNC(DECODE(ww, 53, TO_DATE(yy || '3112', 'yyyyddmm'), TO_DATE(yy || '-' || TO_CHAR(ww * 7), 'yyyy-ddd')), 'd') last_day
  FROM (SELECT SUBSTR('2000-53', 1, 4) yy, TO_NUMBER(SUBSTR('2000-53', 6)) ww
          FROM DUAL)
1 row(s) selected
        LAST_DAY        
-----------------------
            31-Dec-2000

The last piece of code works only for week 53, if you try to run for week 52 it give the same result as week 53.

One code should work for all cases. It is not possible to run diffrent codes for different conditions.

I have created a database function as per the logic I sent earlier. It works fine for all cases.

Thank you.

Gopi.

You did something wrong, this is corect code:

SELECT TRUNC(DECODE(ww, 53, TO_DATE(yy || '3112', 'yyyyddmm'), TO_DATE(yy || '-' || TO_CHAR(ww * 7), 'yyyy-ddd')), 'd') last_day
 FROM (SELECT SUBSTR('2000-52', 1, 4) yy, TO_NUMBER(SUBSTR('2000-52', 6)) ww
         FROM DUAL)

1 row(s) selected
        LAST_DAY        
-----------------------
            24-Dec-2000

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/Refund.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer