Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Why is date still displaying DD-MON-YYYY ?

    Why is date still displaying DD-MON-YYYY ?

    Oracle 10gR2 on XP


    Init.ora looks like this:

    SPFILE='C:\oracle\product\10.2.0\db_2/dbs/spfileorcl.ora'

    I did this.


    ALTER SYSTEM SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MIS' scope=spfile;

    commit;

    SHUTDOWN IMMEDIATE;

    STARTUP;

    But then, date still displaying DD-MON-YYYY ?

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    25-APR-06

    SQL>

    SPFILE NOW LOOKS LIKE THIS.

    orcl.__java_pool_size=4194304
    orcl.__large_pool_size=4194304
    orcl.__shared_pool_size=100663296
    orcl.__streams_pool_size=0
    *.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
    *.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
    *.compatible='10.2.0.1.0'
    *.control_files='C:\oracle\product\10.2.0\oradata\ orcl\control01.ctl','C:\oracle\product\10.2.0\orad ata\orcl\control02.ctl','C:\oracle\product\10.2.0\ oradata\orcl\control03.ctl'
    *.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
    *.db_block_size=8192
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='orcl'
    *.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
    *.db_recovery_file_dest_size=2147483648
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
    *.job_queue_processes=10
    *.nls_date_format='MM/DD/YYYY HH24:MIS'
    *.open_cursors=300
    *.pga_aggregate_target=96468992
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=289406976
    *.undo_management='AUTO'
    *.undo_tablespace='UNDOTBS1'
    *.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
    Last edited by wrwelden; 04-25-06 at 14:48.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The nls_date_format sets the format on the server (blocks, packages, procedures, functions...) Every client machine has it's own in the registery that the local tools will use. Try

    ALTER session SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MIS';

    and see what happens.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    That did work for the session. Then I modified the registry adding a new string value to registry under

    hkey_local_machine/software/oracle

    NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MIS'

    I still get the same results after re-booting,

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    25-APR-06

    SQL>
    any ideas ? Other than I am clueless!

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Put it under your oracle home path in the registry. As for your first question, did you make sure that after you submitted STARTUP oracle was using your spfile ?

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    your PATH for your spfile has forward slashes and backwards slashes.
    That will not work.

    Anyways, just put this in the glogin.sql file:

    $ORACLE_HOME/sqlplus/admin/glogin.sql
    Code:
    alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Another way to skin this cat, evidently

    added new string value to registry under

    hkey_local_machine/software/oracle

    NLS_DATE_FORMAT = 'MM/DD/RRRR'

    Did not work

    Added a system variable , did work

    Right-click on my computer,

    properties, advanced, environment variable, new

    NLS_DATE_FORMAT = MM/DD/RRRR

    then ,

    to cleanup my spfile,

    alter system reset nls_date_format scope=spfile sid='*';


    Another question,


    How do you make sure that after you submit STARTUP oracle is using your spfile ?
    Last edited by wrwelden; 04-25-06 at 16:29.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Set it under your oracle home path. That is, under hkey_local_machine/software/oracle you will see a key called HOMEX where X is a number, set it under there.

    Try 'show parameter spfile' on sql*plus, to see whether or not you're using it.

Posting Permissions

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