Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Post Unanswered: Change date format

    Hi:
    I want to change the nls_date format from the default one to 'dd.mm.yyyy hh24:mi:ss'. Alter session works fine. However I would like to set it at the instance level, and I have set it up in the init.ora file. However the damn thing is not taking effect. If I do a select sysdate from dual it gives the same old date format back. Any idea what to do..
    Thanks in advance.
    Mitra

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Did you restart the database?

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    Yes I did, and still it does not show up

  4. #4
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    You didn't mention the operating system. Is this windoze? Is the alter session done in sqlplus?

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    Sorry. It is win2k, and I checked it from sqlplus client running on windows

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I believe you have to set the NLS_TIMESTAMP_FORMAT string in your init.ora file.

    You'll still want to change the NLS_DATE_FORMAT string. This will change the default format accepted in the TO_DATE( <date>) without a second parameter...

    JoeB

  7. #7
    Join Date
    Feb 2004
    Posts
    5
    Thanks for your help. However even after the change of the nls_timestamp_format, it is not working. Is it something to do with the language, and the country/region of the o/s??

    Thanks
    Mitra

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Originally posted by amitra
    Thanks for your help. However even after the change of the nls_timestamp_format, it is not working. Is it something to do with the language, and the country/region of the o/s??

    Thanks
    Mitra

    I found this btw:

    NLS_LANG and NLS_DATE_FORMAT must be set for NLS_DATE_FORMAT to be used:

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


    I have another question to go along with this:
    Is it correct that this change only alters the default format when using TO_DATE and TO_CHAR? When you pull a date field out of the database, without applying these functions, will the date still show up as 'dd-mon-yy ...'?

    Thanks,
    Chuck

  9. #9
    Join Date
    Jan 2004
    Posts
    370
    Originally posted by amitra
    Thanks for your help. However even after the change of the nls_timestamp_format, it is not working. Is it something to do with the language, and the country/region of the o/s??

    Thanks
    Mitra
    The NLS settings in the session take precedence over any other settings.
    The NLS settings in the client environment override the instance and database settings.
    The NLS settings in the instance override the database settings.
    The NLS settings in the database are used when nothing else overrides them.

    So setting NLS_DATE_FORMAT in the init.ora will have no effect if NLS_LANG or NLS_DATE_FORMAT are set in the client environment.

    HTH

  10. #10
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: Change date format

    Originally posted by amitra
    Hi:
    I want to change the nls_date format from the default one to 'dd.mm.yyyy hh24:mi:ss'. Alter session works fine. However I would like to set it at the instance level, and I have set it up in the init.ora file. However the damn thing is not taking effect. If I do a select sysdate from dual it gives the same old date format back. Any idea what to do..
    Thanks in advance.
    Mitra
    Why dont u try alter system set nls_date_format.

    this should change to the instance.
    Thanks and Regards,

    Praveen Pulikunnu

  11. #11
    Join Date
    Jan 2004
    Posts
    370

    Re: Change date format

    Originally posted by praveenpr
    Why dont u try alter system set nls_date_format.

    this should change to the instance.
    You can't change NLS_DATE_FORMAT with the ALTER SYSTEM command.

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074

    So then how?

    I'm confused. If you want to change these parms in the initialization file, but you're using an spfile, how do you set these values? Shutdown the database, create a pfile from the spfile, update the pfile with the NLS_DATE_FORMAT value, create the spfile from the pfile, and then startup the database with the spfile?

    Thanks & still learning,
    Chuck

Posting Permissions

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