Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.
Try this
CREATE OR REPLACE FUNCTION marcel
(p_date_string IN VARCHAR2) RETURN DATE IS
l_date DATE;
l_dec31 DATE;
ex_invalid_format EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_invalid_format, -01830);
PRAGMA EXCEPTION_INIT(ex_invalid_format, -01841);
BEGIN l_date := TRUNC(TO_DATE(SUBSTR(p_date_string,-4),'YYYY'),'YEAR'); l_dec31 := LAST_DAY(ADD_MONTHS(l_date,12));
LOOP
IF TO_CHAR(l_date,'D-WW-YYYY') = p_date_string THEN EXIT; END IF; l_date := l_date+1; EXIT WHEN l_date > l_dec31;
raise ex_invalid_format;
END IF;
RETURN l_date;
EXCEPTION
WHEN ex_invalid_format THEN
RAISE_APPLICATION_ERROR(-20000,'Invalid Date String - Must be ''D-WW-YYYY''');
END;
/
So we get ...
SQL> select marcel('1-24-2002') from dual;
MARCEL('1-2
CE Received on Thu Jun 13 2002 - 10:42:52 CDT