Re: Dates in Oracle.. a Y2K question....

I must say that you have done your homework and expressed details very clearly. I was sloppy, but IMHO this forum is not for explaining every thing about each aspect of a subject. The poster of a question is supposed to take the ball and run when the question is answered. Most of us do not take too much time away from our regular job to answer questions in this forum. What I try to do is cut and paste from my notes on a subject and if I do have a script I post that too. I do not explain some obvious aspects either.

Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl> wrote in article <6qsr6a$aiq$1_at_newton.a2000.nl>...
> > Oracleguru
>
> Well, aren't we too modest? :-)

******Irrelevent to the subject matter !!!

>
> >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.
>

******I was wrong. Check my corrected post. Sorry to say that it has a typo.

> In most installations, Oracle's default is DD-MON-YY, without the time.
But
> this format is only used when entering or printing dates. It has nothing
to
> do with storing the dates. Oracle always stores the dates with a four
digit
> year, and a timestamp. In fact, Oracle does not store the dates as
strings
> at all. The timestamp could be 0:00:00, but it is always present.
******Obvious facts.

>
> >There is another format rr. It works on two digit years between 1950
and
> >2049 and correctly prepends and stores the 4 digit year.
>
> True. Until we change from 2049 to 2050. Users who by then are used to
> entering 49 for 2049 will suddenly notice that 49 yields 2149 as of the
> system date 2050...

******Most companies do not want to spend money for the event that far in future. Be practical. How ******about year 10000, 100000 ? In year 2050 I plan to be the Chief DBA on St. Peter's project team ******figuring out database for trillions or is it zillions of names, indexes, openspaces not tablespaces, ******parent-child relationships etc. and it may not be even Oracle. Just joking. The technology ******changes so fast, the system in operation today will be definitely replaced in 20 years by something ******better I hope.

>
> >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.
>
> Nor does the RR format. As far as I know, the releases supporting RR also
> support RRRR. But I could be wrong here.
******Yes, you are wrong on this. rrrr on some older 7.0 version also gave 9898.

>
> >As interesting fact, the following yields roman numerals, xcviii.
> >
> >select to_char(98,'rr') ROMAN_NUMERALS from dual;
>
> Just because you are not using to_char for a date, but for a number.
******Obvious fact because 98 is not enclosed in quotes.

>
> >In your case you can have your DBA set it to 'dd-MON-yyyy hh24:mi:ss'.
> >This should work for all centuries.
>
> Please no. Why the full time?

******I agree. I have already corrected by my second post so it should be 'dd-MON-yyyy'.

>
> > Avoid rrrr format.
>
> Why's that?

******Has been answered above. Not every one has 7.3 or 8.0. Some shops still have 6.0, besides ******whoever has heard about rrrr date format outside Oracle but yyyy is a universal format and they ******do about the same thing.

>
> > For 4 BYTE OR 4 DIGIT YEAR, use format yyyy, even though format rrrr
> >works.
>
> Well, when inserting dates, RRRR interprets 98 as 1998. YYYY gives you
0098.
> What do you prefer?

******Only if you let Oracle do conversion implicitly and are using literal. Oracle specifically says not to ******use literals in their manual.
******Do not use: insert into test(date1) values ('12-dec-98') ******Use the correct way: insert into test(date1) values (to_date('12-dec-98','dd-mon-yy'));

>
> > FORMAT rr on a NUMBER field yields Roman numerals. DO NOT USE IT ON
> > 2 BYTE or 2 DIGIT YEAR.
>
> I disagree. Using RR in to_char( some date) is exactly the same as using
YY.
> It just depends on the width you wish to use. RR does not yield roman
> characters when using on dates! By the way, when matching fails Oracle
will
> try other formats anyway. Like specifying four digits for RR will have
> Oracle use RRRR instead of RR. That is: at least in Oracle 8.
******My statement emphasizes NUMBER. Some number columns hold 2 digit year.

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com Received on Thu Aug 13 1998 - 10:40:04 CDT