Re: problems with date format
Date: Fri, 04 Dec 1998 12:35:58 GMT
Message-ID: <OGQ92.468$dF.9176213_at_news.siol.net>
Hm, solution is OK, but it is not language independent.
I have NLS_LANG=SLOVENIAN_SLOVENIA.EE8ISO8859P2
and result of thiw query is:
Connected to:
Oracle8 Release 8.0.4.0.0 - Production
PL/SQL Release 8.0.4.0.0 - Production
SQL> SELECT NEXT_DAY(TRUNC(TO_DATE('&YEAR','YYYY'),'YEAR')-7
2 + (&WEEK-1)*7, 'MONDAY') FROM DUAL;
Enter value for year: 1998
old 1: SELECT NEXT_DAY(TRUNC(TO_DATE('&YEAR','YYYY'),'YEAR')-7
new 1: SELECT NEXT_DAY(TRUNC(TO_DATE('1998','YYYY'),'YEAR')-7
Enter value for week: 30
old 2: + (&WEEK-1)*7, 'MONDAY') FROM DUAL
new 2: + (30-1)*7, 'MONDAY') FROM DUAL
SELECT NEXT_DAY(TRUNC(TO_DATE('1998','YYYY'),'YEAR')-7
*
ERROR at line 1:
ORA-01846: not a valid day of the week
If you want language independent solution, must write function which return day name.
-- Boris ------------------------------- Boris Oblak ABAKUS Kranj, d.o.o. e-mail: boris.oblak_at_siol.net Jurij Modic wrote in message <3665c2e0.12826708_at_news.siol.net>...Received on Fri Dec 04 1998 - 13:35:58 CET
>On Wed, 02 Dec 1998 20:57:51 +0100, Strautmann <stra_at_Strautmann.com>
>wrote:
>
>>hallo,
>>
>>How to get a date (the first day of the week) by input the number of
>>the week
>>for example week 44 get date 26.10.1998
>
>With some basic date arithmetics and functions:
>
>SQL> SELECT NEXT_DAY(TRUNC(TO_DATE('&YEAR','YYYY'),'YEAR')-7
> 2 + (&WEEK-1)*7, 'MONDAY') FROM DUAL;
>Enter value for year: 1998
>Enter value for week: 44
>
>NEXT_DAY(
>---------
>26-OCT-98
>
>SQL> /
>Enter value for year: 1997
>Enter value for week: 44
>
>NEXT_DAY(
>---------
>27-OCT-97
>
>SQL>
>
>>thanks
>
>Regards,
>
>Jurij Modic <jmodic_at_src.si>
>Certified Oracle7 DBA (OCP)
>================================================
>The above opinions are mine and do not represent
>any official standpoints of my employer