Re: Problem with date conversion

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/07/30
Message-ID: <5rogib$ncm@info.csufresno.edu>#1/1

In article <870169462.15286_at_dejanews.com>,  <bthomas_at_eises.equipement.gouv.fr> wrote:
>Hello.
>
>I want to calc the month when I know the year (YYYY) and the week (WW).
>If I try the query :
> SELECT TO_CHAR(TO_DATE('199701', 'YYYYWW'), 'MM')
> FROM dual;
>I got the error 'ORA-1820, format code cannot appear in date input format'
>

The reason is that a week covers any one of seven days, so you cannot determine the exact date for a given week. You can take the week and multiply by 7 to get roughly the correct date.

The select below finds the date of the 4th day of the given week, and returns the month for that date. In the example, the week is 48:

SELECT
  TO_CHAR(
    TO_DATE(

          LTRIM(
             TO_CHAR(4+7*(TO_NUMBER(SUBSTR('199748',5,2))-1),'099'))
                                 || SUBSTR('199748',1,4),'DDDYYYY')

    ,'MM') "MONTH"
FROM DUAL; Regards,
Steve Cosner Received on Wed Jul 30 1997 - 00:00:00 CDT