date datatype

[manjunathk]

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]

[bkanti]

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

[manjunathk]

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

[bkanti]

Try to change that in init<sid>.ora

[manjunathk]

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

[denevge]

If you change something in init.ora, you have to reboot the db before it takes effect.

Regards
Gert

[rsuri]

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

[pwoneill]

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.