Re: Problem with date conversion

From: Emre Omacan <omacan_at_pemail.net>
Date: 1997/08/03
Message-ID: <870658939.1663@dejanews.com>#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'
>
> Can you help me, please ?
>
> And it's better if you can send a copy by mail...
>
> Thank you.
>
> Bruno Thomas <mailto:bthomas_at_eises.equipement.gouv.fr>
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

The error you got says that the format element 'WW' can only appear in date to char conversions. Try the function below:

function month_from_week(week in number, year in number) return number is

  first_day    date;
  first_monday date;
  day_at_week  date;

begin
  first_day := to_date('0101' || to_char(year, '0000'), 'ddmmyyyy');   first_monday := next_day(first_day, 'MONDAY');   day_at_week := first_monday + (week - 1) * 7;   return to_number(to_char(day_at_week, 'MM')); end;

This function obviously starts with the first whole week of the year. But you can easily modify it for your own needs and add some parameter validation code.

Good Luck.

Emre

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Sun Aug 03 1997 - 00:00:00 CDT