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

There are some ambiguities in your terms so let me state my assumptions:

1. You mean 'week' in the same sense as Oracle's 'WW' format model
2. You mean 'day number' in the same sense as Oracle's 'D' format model

Honestly, I think at least one of those assumptions is likely to be false. It seems inconsistent to use 7 days per week starting on the first day of the year (1) but have the day numbers of the weeks always start on Sunday (2). I'm not saying that Oracle is inconsistent only that using these two models together would seem inconsistent.

select case when to_char(to_date('01-JAN-' || :year, 'DD-MON-YYYY'), 'D') > :day
then to_date('01-JAN-' || :year, 'DD-MON-YYYY') + (:week - 1) * 7   + (:day + 7 - to_char(to_date('01-JAN-' || :year, 'DD-MON-YYYY'), 'D'))
else to_date('01-JAN-' || :year, 'DD-MON-YYYY') + (:week - 1) * 7   + (:day - to_char(to_date('01-JAN-' || :year, 'DD-MON-YYYY'), 'D')) end
from dual

Richard

Marcel Raap wrote:
>
> 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.
Received on Mon Jun 10 2002 - 13:10:15 CDT