-
OK are you ready for the question of the week?
I did some mods to the init.ora for our production DB last fri night. But looking in instance manager I found that the changes I made the week before werent showing up anymore, I had changed the shared_pool_size from 18mb to 28mb, then checked it after i restarted and all was cool, then I happened to look in instance mgr the other day and found it was back to 18mb which led me to believe that the box was rebooted and they have the db set to start on reboot pointing to a diff init.ora. Sure enough I traced a few files with the sysadm and it was pointing to a different init.ora when its restarted. Now I looked in the init.ora they were usiing and everything looks the same EXCEPT this line..
"nls_date_format = "DD-MON-RR" # 1/6/00 JLC to handle Y2K"
without that in what is the date format my DB is using, and is this a biggggg boo boo???
-
:) What is your question?
What RR means?
Year inserted with RR stores with YY stores
10 2010 1910
49 2049 1949
51 1951 1951
99 1999 1999
-
Yes, you need the DD-MON-RR format in the database now - It was a year 2k requirement so that any year typed in with a 2 digit year would be interpreted as:
> 50 = 19xx
< 50 = 20xx
I believe it needs to be set in the client environment too.
Oracle stores dates as seconds since 4719bc (Roughly?) so NLS_DATE_FORMAT is purely a date mask.
-
ok but without the line
"nls_date_format = "DD-MON-RR" # 1/6/00 JLC to handle Y2K"
what will the nls_date_format default to???
thanks,
SM
-
If will default to DD-MON-YY.
This is certainly the case for 7.x and possibly 8.1.5 as well - not sure about other versions. I think could well be the same, except the default init.ora already contains DD-MON-RR.
-
Two Oracle tech notes (available on MetaLink) that should be read by all concerning dates are 67423.1 and 69029.1. In particular, client settings can override the NLS_DATE_FORMAT set in the init.ora, and NO implicit date conversion is Y2K safe. Don't rely on any init parameter to prevent date conversion problems.
