Re: How to transfer year, week, day to date

mraap_at_yahoo.com (Marcel Raap) wrote in message news:<95eb55e.0206100345.3771bfd6_at_posting.google.com>...
> Hi,
>
> I need a SQL-statement (or PLS/SQL function) that accepts a year, a
> week number and a day number as input and returns a date.
>
> I thought of doing it like this:
>
> select to_date('1-24-2002', 'D-WW-YYYY')
> from dual;
>
> but for some reason this is not allowed in SQL*Plus.
>
> Anyone suggestions???
>
> Thanks!
>
> Marcel.

Week is not a valid format to input in a date. You can get a Week from a date as a to_char but not other way around.

SELECT to_char(sysdate, 'D-WW-YYYY') from dual; will give you "Day of week"-"Week of year"-"Year"

SELECT to_char(sysdate, 'DD-W-YYYY') from dual; will give you "Day of Month"-"Week of Month"-"Year"

You would need to write a function to extract date from week of year and day of week.

Hint: -- Get first day of year (01-01-<Input year>)
      -- Loop 7 days at a time until week equals the input week
      -- Get day of week from the above week
      -- Either add or subtract days to synchronize with your input
day
      -- Convert to_char to to_date and return

You might have to tweek it a bit to work. Received on Mon Jun 10 2002 - 17:28:19 CDT