Re: How to check whether my Oracle database has Y2K problem?

Hi Suresh:

    I have to agree with Yuri. If you are dealing with an existing database and various front end products/applications you may not be able to ensure that everything, everywhere has dates in YYYY format. So while you're waiting for that happy day to arrive RRRR is the best compromise for immediate use.

    The fact that other databases don't have an RRRR is not particularly important unless you're swicthing databases frequently. Van

Oracleguru, Suresh Bhat wrote in message <01be56ef$e062dae0$a504fa80_at_mndnet>...
>This was posted in error and have flaws in it.
>
>Please disregard its content
>
>Thanks
>
>
>Oracleguru
>
>Oracleguru, Suresh Bhat <oracleguru_at_mailcity.com> wrote in article
><01be55e7$63ade660$a504fa80_at_mndnet>...
>> Oracle does not really store 2 digit year.
>>
>> It all depends what is set in your initXXXX.ora for nls_date_format
>> attribute.
>> Oracle's default is 'dd-MON-yy hh24:mi:ss'. YY format simply assumes
>that
>> the year is in the current century and prepends 19 to the two digit year.
>>
>> There is another format rr. It works on two digit years between 1950
>and
>> 2049 and correctly prepends and stores the 4 digit year.
>>
>> There is no yyrr or rryy format, what you will get is 9898. Even though
>> rrrr format works, it may not work in older versions of 7.0 Oracle
>release.
>>
>> As interesting fact, the following yields roman numerals, xcviii.
>>
>> select to_char(98,'rr') ROMAN_NUMERALS from dual;
>>
>> -----------------------------------------------------
>>
>> In your case you can have your DBA set it to 'dd-MON-yyyy hh24:mi:ss'.
>> This should work for all centuries.
>>
>> Following is another way to use is it just for your SQL*Plus session:
>>
>> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
>>
>> -----------------------------------------------------
>> Rules for TO_DATE function:
>>
>> FORMAT rr SHOULD BE USED FOR 2 BYTE OR 2 DIGIT YEAR.
>>
>> FORMAT yyyy SHOULD BE USED FOR 4 BYTE OR 4 DIGIT YEAR.
>>
>> DO NOT USE yy format. Avoid rrrr format. There is NO yyrr or rryy
>> format.
>>
>> For 4 BYTE OR 4 DIGIT YEAR, use format yyyy, even though format rrrr
>> works.
>> Format rrrr did not work in Release 7.1.
>>
>> ------------------------------------------------------
>>
>> Rules for TO_CHAR function:
>>
>> ALWAYS USE yyyy format. yy format works but you may not know the
>actual
>> date that is being converted. There is NO yyrr or rryy format.
>>
>> FORMAT rr on a NUMBER field yields Roman numerals. DO NOT USE IT ON
>> 2 BYTE or 2 DIGIT YEAR.
>>
>>
>> Good lunck !!!
>>
>> Oracleguru
>> Suresh Bhat
>> www.oracleguru.net
>> oracleguru_at_mailcity.com
>>
>> Violin <violin.hsiao_at_mail.pouchen.com.tw> wrote in article
>> <36c226fa.258671_at_news.twsc.pouchen.com.tw>...
>> > Hello,
>> > Our production Database is Oracle 7.3.2 on NetWare 4.10.
>> > I connect as internal/password
>> > select * from props$ where name = 'NLS_DATE_FORMAT';
>> > And return the date format : DD-MON-YY
>> > Does this database has Y2K problem?
>> > If yes,how to change the NLS_DATE_FORMAT?
>> > Thank you for any help!
>> >
>> > Violin.
>> > violin.hsiao_at_mail.pouchen.com.tw
>> >
>>
Received on Sat Feb 13 1999 - 16:50:30 CST