| year 2000 [message #365391] |
Thu, 16 April 1998 23:18  |
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  |
Frank Naude
Messages: 4579
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 |
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 |
Frank Naude
Messages: 4579
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 |
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 |
Frank Naude
Messages: 4579
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
