how to get date from week and day of the week in oracle

Question

i am having week number and day of that week then how can i get date

example

week number=20 and day of that 20th week is friday then ? what is date?

Answer 1

It looks messy but this will probably do the trick, or at least form a starting point for you:

select TO_CHAR(
       trunc(sysdate,'YYYY')
       + ((20-1) * 7)
       + (to_char(trunc(sysdate,'YYYY')
          + ((20-1) * 7),'D')
          - 5)
       ,'"Wk"WW DY DD-MON-YYYY') from dual;

Wk20 FRI 21-MAY-2010

This starts with the first day of the year, adds 20 x 7 days, then adds enough days after that to get the next Friday.

In your locale, day 5 might not be Friday - so you might have to adjust this parameter.