Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to transfer year, week, day to date

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

From: Charlie Edwards <Charlie3101_at_hotmail.com>
Date: 13 Jun 2002 08:42:52 -0700
Message-ID: <db479d88.0206130742.3fba2805@posting.google.com>


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;

  END LOOP;   IF l_date > l_dec31 THEN

     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



16-JUN-2002 Regards

CE Received on Thu Jun 13 2002 - 10:42:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US