Home » SQL & PL/SQL » SQL & PL/SQL » Year Format
Year Format [message #188962] Tue, 22 August 2006 10:32 Go to next message
Prince
Messages: 32
Registered: December 2001
Member
What is the advantage of using RRRR rather that YYYY ... is it mandatory? Are there any disadvantages of sticking to YYYY?

Thanks.
Re: Year Format [message #188972 is a reply to message #188962] Tue, 22 August 2006 11:06 Go to previous messageGo to next message
fahadsami
Messages: 31
Registered: May 2006
Member
Hi,

Oracle has always stored dates in four digits, regardless of the
NLS_DATE_FORMAT setting in the parameter file (init.ora). Having said that,
it is still essential to understand where problems may arise! It doesn?t
matter whether you use ?YYYY? or ?RRRR? for the TO_CHAR function (character
representation of a date), as this function doesn?t actually convert the
date. You should instead use ?RRRR? for the TO_DATE function, which does
convert dates to a Y2K-compliant format.

So what?s the difference between YY, YYYY, RR, and RRRR? YY
allows you to retrieve just two digits of a year, for example, the 99 in
1999. The other digits (19) are automatically assigned to the current
century. RR converts two-digit years into four-digit years by rounding.
50-99 are stored as 1950-1999, and dates ending in 00-49 are stored as
2000-2049. RRRR accepts a four-digit input (although not required), and
converts two-digit dates as RR does. YYYY accepts 4-digit inputs but doesn?t
do any date converting

Essentially, the differences between using RR, RRRR, YY, and YYYY come down
to differences in how an Oracle database stores and displays values. As an
example, consider the following scenario. A simple SELECT statement of the
date column is done without the use of the TO_DATE (or TO_CHAR) function.

USING
ENTERED
STORED
SELECT of date column

YY
22-FEB-01
22-FEB-1901
22-FEB-01

YYYY
22-FEB-01
22-FEB-0001
22-FEB-0001

RR
22-FEB-01
22-FEB-2001
22-FEB-01

RRRR
22-FEB-01
22-FEB-2001
22-FEB-2001


If you have dates which need to be made Y2K compliant, use the TO_DATE
function and DD-MM-RRRR:

UPDATE ORDER

SET order_date = TO_DATE (order_date, ?DD-MON-RRRR?);

There are instances, however, in which RR or RRRR won?t convert dates to the
Year 2000. For example, if the original dates were entered as four digits
then the TO_DATE function with RR (or RRRR) will not change that value to a
Year 2000 date.

Thanks

Fahad
Re: Year Format [message #188984 is a reply to message #188972] Tue, 22 August 2006 12:00 Go to previous message
Prince
Messages: 32
Registered: December 2001
Member
Thanks ... well explained.
Previous Topic: tablespace
Next Topic: to find out % of total
Goto Forum:
  


Current Time: Tue Dec 06 02:26:12 CST 2016

Total time taken to generate the page: 0.12605 seconds