| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Which Mask is Better: YYYY or RRRR?
dhmac schrieb:
>
> Which is the best mask to use for NLS_DATE_FORMAT - DD-MON-YYYY or DD-MON-RRRR?
From oracle y2000 compliance:
3.6 Notes on Current Products
3.6.1 Products That Do Not Manipulate Data
These products do not carry out any data manipulation. Therefore, they are by their very nature, Year 2000
Compliant.
3.6.2 The Oracle Server
The Oracle Server is Year 2000 Compliant.
Applications that use the Oracle RDBMS (Oracle7 Server and Oracle8 Server) and exploit the DATE data type (for
date and/or date with time values) need have no concerns about their stored data when the year 2000 approaches.
The Oracle7 Server and Oracle8 Server DATE data type stores date and time data to a precision that includes a
four-digit year and a time component down to seconds (typically YYYY:MM:DD:HH24:MI:SS). No operational problems are expected with the Oracle Server, networking and system management products.
Oracle's Development Organization has conducted tests of various Year 2000 operational scenarios to verify that
there is no impact to users at the turn of the century. These scenarios included tests of replication, point-in-time
recovery, distributed transactions, system management and networking features, across timezones, datelines, and
centuries.
The Oracle RDBMS has always stored dates using a four-digit year (the OraDate format), hence customers using
the DATE data type should not have any application level problems. To facilitate year 2000 compliance for
applications that use the two-digit year format the Oracle7 Server and Oracle8 Server provides a special year format
mask RR. Using the RR format, any two-digit year entered will be converted thus:
Current Year: Last Two Digits
Two-Digit Year Specified
Year RR Format Returns
0-49
0-49
Current Century
50-99
0-49
One Century after current
0-49
50-99
One Century before current
50-99
50-99
Current Century
Therefore, regardless of the current century at the time the data
is entered the 'RR' format will ensure that the year
stored in the database is as follows:
If the current year is in the second half of the century (50 - 99)
and a two-digit year between 00 and 49 is entered: This
will be stored as a next century year. E.g. 02
entered in 1996 will be stored as 2002.
and a two-digit year between 50 and 99 is entered: This
will be stored as a current century year. E.g. 97
entered in 1996 will be stored as 1997.
If the current year is in the first half of the century (00 - 49)
and a two-digit year between 00 and 49 is entered: This
will be stored as a current century year. E.g. 02
entered in 2001 will be stored as 2002.
and a two-digit year between 50 and 99 is entered: This
will be stored as a previous century year. E.g. 97
entered in 2001 will be stored as 1997.
The RR date format is available for inserting and updating DATE
data in the database. It is not required for
retrieval/query of data already stored in the database as Oracle
has always stored the YEAR component of a date in
its four-digit form.
Further details concerning the 'RR' DATE format mask can be found
in the Oracle Technical Bulletin Reference.
104296.626.
Oracle7 Release 7.0 and prior releases of the Oracle server have
not been tested for Year 2000 compliance. Users
who must run on a Year 2000 certified database should therefore
make sure they use a release 7.1 or later version of
Oracle. This includes all components of the database.
Oracle7 Release 7.0 does not have support for NLS_DATE_FORMAT.
Starting with Release 7.1, it is supported.
3.6.2.1 Dates Stored In Character Data Types
Where applications use character strings in CHAR or VARCHAR2 data types, if century information is not
maintained, the application willl require modification to include routines to ensure that such dates are treated
appropriately when affected by the change of the century. This can be either done by changing the strings to maintain
century information or, with certain constraints, by using the 'RR" date format when interpreting the string as a date.
For new applications, or when carrying out the modifications to ensure dates stored as character strings are
Year-2000 compliant, it is advisable to convert such dates to use the Oracle DATE data type, thus ensuring year
2000 compliance, or if this is not feasible then to store the dates in canonical form which is language and format
independent, and which handles full years. For example YYYY/MM/DD , plus if necessary the time element as
hh24:mi:ss. Dates stored in this form must be converted to the correct external format whenever they are displayed
or received from users or other programs.
The format 'YYYY/MM/DD HH24:MI:SS' has the following advantages:
it is language independent, i.e.the months are numeric
it has the full four-digit year,so centuries are unambiguous
the time is represented fully, themost significant elements
occur first, so character based sorts operations sort the
dates correctly.
One disadvantage is that there is no support for dates BC.
3.6.3 Programming Hints and Tips
In this section we describe some common programming errors which
may look like a incorrect Year 2000 processing
by the database engine. But on closer inspection they are based on
the incorrect use of the existing programming
facilities.
Some applications store years as 2 characters or a 2-digit number,
i.e., '98' instead of '1998'. This may lead to
unpredictable results when 20xx dates are mixed with 19xx dates.
You should modify your applications to use the full
4-digit year, and correct the data in the database. In other
applications, a 4-digit year is meant to be stored, but bugs
in some applications incorrectly store some 2-digit year rows with
the other 4-digit rows. This will lead to
unpredictable results for queries by date if date fields can go
back to dates earlier than 1900. An application can
check for rows which contain dates earlier than 1900 and flag this
error.
Many applications in the past have defined the year of a date with 2 characters or 2 digits on order to save disk space.
To simplify the processing of this 'YY' format of the year Oracle introduced the RR format and the rules how the RR
format is internally processed by the data server (see the previous section 3.1.2 which describes these rules in detail).
It is not unusual that the rules of the RR format mask are not fully observed by the application programmer which may
lead to unexpected results. Remember, the RR format mask operates correctly between the years 1950 and 2049.
Applications should be inspected if they process dates prior to 1950 or later than 2049 and store the year as 2-digits.
If both conditions are met such applications should not use the RR format but should expand the 2 digit year 'YY ' into
a for digit year 'YYYY' and store the 4 digit number into the database.
Here is another more unusual error which helps to understand the interaction between NLS_DATE _FORMAT and
the Oracle RR format mask. This is syntactically a correct statement but it has a logical flaw:
Assume this is the statement you are trying to run:
SELECT TO_CHAR(TO_DATE(LAST_DAY('01-FEB-00),'DD-MON-RR'),
'MM/DD/RRRR') FROM DUAL;
The above select when run will return the following: 02/28/2000.
This appears wrong on the surface, but it is not.
The operation is using the default NLS_DATE_FORMAT, which is
'DD-MON-YY'. If I change the
NLS_DATE_FORMAT to 'DD-MON-RR', then the same select returns this:
02/29/2000.
This change must be done via an ALTER SESSION command BEFORE issuing the above select statement. In fact, if
I do a 'SELECT LAST_DAY('01-FEB-00') FROM DUAL;' the results will change depending on our
NLS_DATE_FORMAT. With YY, the LAST_DAY is 28-Feb-00. With RR, it is 29-Feb-00. This is correct, as
1900 is NOT a leap year, but 2000 is.
The logic of the original SELECT statement is flawed. Consider this SELECT in the following manner:
SELECT TO_CHAR(TO_DATE(LAST_DAY('01-FEB-00),'DD-MON-RR'), 'MM/DD/RRRR') FROM DUAL; The first function we execute is the inside function, LAST_DAY.
Because NLS_DATE_FORMAT is YY, this correctly returns 2/28,because it is using the year 1900!! From here on
out, that is the value that is ALWAYS USED FOR THE REST OF THESE FUNCTIONS. We do a TO_DATE,
then a TO_CHAR, but all along, we are using 2/28! So even though we format the output as prescribed by the
SELECT statement and make it look like it is a Year 2000 date that is displayed (note that we make it display as
02/28/2000) it really is just using valid numbers derived from inner function, and then wrapping it in a bow to look like
a bug.
Another lesson can be learned from this example: there can be a difference how dates are displayed to the user and
how dates are processed internally. What is important for the Year 2000 compliance is the correct and consistent
internal processing by the Oracle Server.
More details at: http://www.oracle.com/year2000/2000/2000.htm
HTH
Matthias
--
grema_at_t-online.de
Protect privacy, boycott Intel: http://www.bigbrotherinside.org Received on Thu Mar 04 1999 - 01:58:13 CST
 |
 |