Home » Other » General » year 2000
year 2000 [message #365391] Thu, 16 April 1998 23:18 Go to next message
William Behrmann
Messages: 1
Registered: April 1998
Junior Member
We downloaded some info on adjusting our version(7.2) of Oracle for the year 2000. When we looked at the init.ora file, we didnt find anything relating to the date format and we were wonderiing if we should still go ahead and insert the new format into that file? If so, where should it be put and what would the exact syntax be? Thank u for your time and input.

William Behrmann
Re: year 2000 [message #365397 is a reply to message #365391] Mon, 20 April 1998 16:50 Go to previous messageGo to next message
Frank Naude
Messages: 4571
Registered: April 1998
Senior Member
The default date format can be changed for a database by setting NLS_DATE_FORMAT= in your INIT.ORA file. The default date format can also be changed per session. Eg:

ALTER SESSION SET NLS_DATE_FORMAT=DD-MON-RRRR';

You can also set the default date format per statement. Eg:

SELECT TO_DATE('01-JAN-00', 'DD-MON-RR') FROM ...


Cheers
Frank
Re: year 2000 [message #365401 is a reply to message #365397] Sun, 26 April 1998 11:03 Go to previous messageGo to next message
Sue Paulson
Messages: 2
Registered: April 1998
Junior Member
I hate to nit-pick, but 'DD-MON-RRRR' (ALTER SESSION example) is incorrect. Use 'DD-MON-RR' or 'DD-MON-YYYY'.

Setting the format to 'DD-MON-YYYY' in the init.ora file could impact the display in a GUI dialog or printed report if space is provided only for a 2-digit year. Use 'DD-MON-RR' until you are sure that all applications can display a 4-digit year.

Hope that helps,

Sue
Re: year 2000 [message #365402 is a reply to message #365401] Sun, 26 April 1998 11:15 Go to previous messageGo to next message
Frank Naude
Messages: 4571
Registered: April 1998
Senior Member
Not true, according to my Oracle Server SQL Reference guide:

RR - Round year with 2 digits.

RRRR - Round year. Accepts either 4-digit or 2-digit input.

For more info, read chapter 3 Date Format Elements.

Cheers
Frank
Re: year 2000 [message #365403 is a reply to message #365402] Sun, 26 April 1998 13:23 Go to previous messageGo to next message
Sue Paulson
Messages: 2
Registered: April 1998
Junior Member
I looked. I still do not see a reference to 'RRRR'. I am looking in Chapter 3 of 7.3 SQL reference, table 3-14. I also performed a document search for 'RRRR' and found nothing.

So, anyway, I tried it in SQL*Plus ( select to_char(sysdate,'mm/dd/rrrr') from dual; ) and it worked fine.

Does 'RRRR' behave differently than 'YYYY'?

Sue
Re: year 2000 [message #365407 is a reply to message #365403] Tue, 28 April 1998 00:33 Go to previous messageGo to next message
Frank Naude
Messages: 4571
Registered: April 1998
Senior Member
I've used the Oracle 8.0.3 SQL Reference guide. As far as I know, the RRRR date format mask will work from Oracle 7.2 upwards.

RRRR will accept 2-digit and 4-digit years, YYYY will ONLY accept 4-digit years.

With RRRR "01-JUN-98" and "01-JUN-1998" will be accepted by the TO_CHAR function and both will be stored with year 1998. Also, "01-JUN-01" and "01-JUN-2001" will be accepted and stored with year 2001.

Cheers
Frank
Re: year 2000 [message #365725 is a reply to message #365397] Tue, 16 November 1999 07:45 Go to previous message
badercheema
Messages: 2
Registered: November 1999
Junior Member
I have tried to put
NLS_DATE_FORMAT='DD-MON-RRRR'
in the Personal Oracle 7.3.3 initorcl.ora
and when I startuo DB and execute this select query:
SELECT MONTHS_BETWEEN('01-MAR-99','01-MAR-00') FROM DUAL;
the result is 1188 not -12
It work only after this command:
ALTER SESSION SET NLS_DATE_FORMAT=DD-MON-RRRR';
what should I do to make it automaticly Y2K compliant.
Previous Topic: Why Oracle?
Next Topic: Automation
Goto Forum:
  


Current Time: Tue Feb 07 11:28:16 CST 2023