Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: Urgent - nls_date_format

    Hi

    50 users connecting to 10g.

    Different users need different date formats returned.

    How do we achieve this...

    I tried this.. did not work..

    go to HKEY_Local_Machine, Software, Oracle and right
    click on oracle and added string and named it nls_date_format and double
    clicked the box and typed 'mm/dd/yy hh:mi:ss'.

    Anywhether or more effiecient way?

    Any help greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ALTER SESSION SET NLS_DATE_FORMAT=[what each individual wants here];
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    trigger on login to set NLS_DATE_FORMAT
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2004
    Posts
    5
    When they access the application the date should return mm/dd/yy hh mi ss
    The application is Client server.

    altering session might not be possible...

    Writing a trigger is the only way?

    In simple words.

    default - oracle returns dd-mon-yyyy

    i want it return mm/dd/yy hh mi ss for all users

    How to do it ?

    Any help greatly appreciate...

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    <<Different users need different date formats returned.>>
    The_Duck gave you the answer for this ... Logon Trigger is a good way
    to handle this ... This is a quick and easy trigger to write ...

    if <<i want it return mm/dd/yy hh mi ss for all users>> is what you really
    want, then why not just set the nls_date_format parameter in the INIT parameters ???

    Gregg

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    simple as below just alter db instead of session:
    PHP Code:
    topicadm@Topic_Devselect sysdate from dual;

    SYSDATE
    -----------------
    Aug 17 2004 14:42

    topicadm
    @Topic_Devalter session set nls_date_format 'mm/dd/yy hh mi ss';

    Session altered.

    topicadm@Topic_Devselect sysdate from dual;

    SYSDATE
    -----------------
    08/17/04 02 50 31 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2004
    Posts
    5
    In the initparameter file i set

    I have nls_lang as america.america

    nls_date_format="mm/dd/yyyy hh:mi:ss"

    This did not work. This is the first thing I did.
    Am i doing anything wrong...?

    I dont want it at the session level.



    should bounce the server or anything else?


    All user's should get their date format as "mm/dd/yyyy hh:mi:ss" that is the problem

    I can write a logon trigger.

    Is there any other way to do it...?

    I THANK YOU ALL FOR POSTING REPLIES TO THIS...THIS HAS BEEN GREAT GREAT HELP.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by orafresh
    In the initparameter file i set
    nls_date_format="mm/dd/yyyy hh:mi:ss"

    This did not work.
    bounce the database but make sure you are not using spfile.
    if so, be sure to make spfile reflect the nls_date_format.

    also keep in mind that nls_date_format is CLIENT based.
    so each machine can overide your settings if they have
    a default format already set (in regedit or wherever).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    nls_date_format="mm/dd/yyyy hh:mi:ss"

    Try

    nls_date_format="mm/dd/yyyy"
    instead .. Oracle assumes timestamp ..

    HTH
    Gregg

Posting Permissions

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