year 2k century issue 1

[smacattack]

I think this is an OS problem and not ORACLE but the problem i have is..
When i do a query on dates it brings back anything from 01-49 as a 2000 date and 50-99 as a 1900 date.
All good and well you might say.
But i want it to reflex 01-99 as 2000 dates ....
How this be changed.
It works fine on our old numaQ machine which i'm sure a setting was changed to accomodate this but what?
Is it a change on AIX or is it a change on oracle?


Anybody HELP!

cheers

 

[bi]

What version of Oracle? If you are using a Y2K compliant version of Oracle, I think it is an Oracle problem because of Oracle's RR date format.

 

[bi]

And what version AIX?

 

[usige]

bi is right. Oracle versions that are Y2K ready (7.3.4(?) and up) set the default date format to 'YY-MON-RR'. You can force it back to the pre Y2K format like:

alter session set NLS_DATE_FORMAT='DD-MON-YY'

You could also change the default format via the various login.sql and init.ora methods.

Hope that helps.

 

[smacattack]

aix version 4.3.3
and oracle 8.1.7
The reason i ask is that on our current live machine numaQ oracle 7.3.4 the date query returns 2000 century for 01-99 and the nls_date_format has not been changed.

On the Aix the oracle nls_date_format has also not been changed to anything but the default.
As both versions are oracle compliant with yr2k this is what made me think it was related to system date at OS level.

????

 

[bi]

Oracle made a change in the default NLS_DATE_FORMAT at version 8.1.6, so they are different for your 7.3.4 database and your 8.1.7 database.

In addition, there is a large difference between Oracle 7.3.4 and 8.1.7. I think your 7.3.4 database is probably using the default (at that time) of YY. Your 8.1.7 database is using the default RR format, and returning 1953 instead of 2053 is the expected behavior.

I think you should ask this question in one of the ORacle forums.