Re: Problem with date conversion

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/29
Message-ID: <33ddeb4e.25045439@www.sigov.si>#1/1

On Tue, 29 Jul 1997 05:01:22 -0600, 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'
>
>Can you help me, please ?
>

When you enter a date you can use only specify year, month, day and time portion of the date. All other format masks (including 'WW' for week of the year) can be used only for formating the date output, i.e. for displaying the date values.

So, for your task you must use DECODE function or something similar. Here is one of the sollution provided that you have string variable &X in the format of 'YYYYWW':

SELECT DECODE (1,
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'02','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'JAN',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'03','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'FEB',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'04','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'MAR',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'05','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'APR',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'06','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'MAY',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'07','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'JUN',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'08','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'JUL',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'09','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'AUG',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'10','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'SEP',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'11','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'OCT',
  SIGN(TO_CHAR(TO_DATE(SUBSTR('&&X',1,4)||'12','YYYYMM'),'WW')-        SUBSTR('&&X',5,2)), 'NOV', 'DEC') FROM dual;

>And it's better if you can send a copy by mail...
>
>Thank you.
>
>Bruno Thomas <mailto:bthomas_at_eises.equipement.gouv.fr>

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jul 29 1997 - 00:00:00 CDT