NLS date format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: NLS date format

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    103

    Question

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


  2. #2
    :) 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
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    Join Date
    Sep 2000
    Posts
    128
    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.

  4. #4
    Join Date
    Oct 2000
    Posts
    103
    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

  5. #5
    Join Date
    Sep 2000
    Posts
    128
    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.

  6. #6
    Join Date
    Sep 2000
    Posts
    47
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width