Re: Problem with date conversion

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

I believe the problem is that the mask in a TO_DATE() function must be interpretable as a single day/date.
Try TO_CHAR(TO_DATE('01011997','DDMMYYYY')+7*(wknum - 1) +xx,'YYYYMM') where xx is the day offset (+ or -) of the start of week 1 from 1st Jan.

HTH Chris Ellis

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"