DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: date datatype

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    hi friends,
    in the date related columns, i am getting values in mm/dd/yyyy format. i need the full datestamp including the hours,minutes and seconds. I tried to change the nls_date_format to include those without success. How to change the format?
    I am using 8.0.5 on a digital unix machine
    thanks
    manjunath

    [Edited by manjunathk on 12-14-2000 at 06:38 PM]

  2. #2
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    Try this
    SQL> SELECT sysdate from dual;

    SYSDATE
    ---------
    14-DEC-00

    SQL> ALTER SESSION
    2 set nls_date_format = "MM/DD/YYYY";

    Session altered.

    SQL> SELECT SYSDATE from dual;

    SYSDATE
    ----------
    12/14/2000

    SQL> alter session set nls_date_format = "MM/DD/YYYY :HH:MI:SS"
    2 /

    Session altered.

    SQL> SELECT SYSDATE from dual;

    SYSDATE
    --------------------
    12/14/2000 :06:46:53
    Soumya
    still learning

  3. #3
    Join Date
    Oct 2000
    Posts
    211
    thanks soumya,
    but this will only work for that particular session.
    I am interested in making the change permanent and across all users
    thanks
    manjunath

  4. #4
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    Try to change that in init<sid>.ora
    Soumya
    still learning

  5. #5
    Join Date
    Oct 2000
    Posts
    211
    i did change the nls_date_format="MM/DD/YYYY :HH:MI:SS" .
    However, the change is not reflected in the values.
    I have not restarted the database, as I think this is a dynamically modifiable parameter right?
    Thanks
    manjunath

  6. #6
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    If you change something in init.ora, you have to reboot the db before it takes effect.

    Regards
    Gert

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Shouldn't you be using TO_CHAR function for this ?

    select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss') from dual;

    You can change the format to many other available forms.

    - Rajeev

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    I think he wanted it default though, but that's right, if everthing used to_char and the format it would work.

    Depending on how your users are accessing the database you could dynamically change their NLS_DATE_FORMAT environment variable to the new format and it will use the new one as default.

    Otherwise if you change it in the init.ora you will need to bounce the database.

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