Date return

[lygine]

Hi Expert,

I have problem to get "Date" element return.

In my table A has 3 columns which is YEAR , no_of_week, and day. Data is looked like this :

year no_of_week day
----- --------------- -----
1999 46 5
(YYYY) (WW) (D)

I would like to combine this 3 columns and then return for me a actual date data which is "1999-NOV-16"

I tried to do as following but i got error:

SQL> select to_date('1999 46 5','YYYY WW D') from dual;
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Please help me to solve the problem.

Lygine*16/11/2K

[balamv]

The statement what you tried doesn't work because you can find a week on given date and not wise versa.

So i think you need to wrote some thing like this.


select decode(mod(to_number(YEAR),4),0,to_date('01-01-'||YEAR,'dd-mm-yyyy') + 1,to_date('01-01-'||YEAR,'dd-mm-yyyy') )+ WEEKS*7 +DAYS from dual


BMV