This content has been marked as final. Show 5 replies
There is no such things as universal on this planet or with Oracle. There are defaults by country and language. You choose what you want on installation or determine how you want output displayed.
Here's how Oracle ALWAYS stores date-time values.
Here's how I choose to display it.
SQL> create table demo ( 2 mycol DATE); Table created. SQL> insert into demo 2 values (SYSDATE); 1 row created. SQL> select dump(mycol) 2 from demo; DUMP(MYCOL) ------------------------------------ Typ=12 Len=7: 120,113,2,16,8,41,10
Here is how I made that choice in my glogin.sql file.
SQL> SELECT * FROM demo; MYCOL -------------------- 16-FEB-2013 07:40:09
A full listing of possible format options can be found here:
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
thank u ,
basically i do not want to change default setting.
but what i under stood is if any client uses differnt envrionment setting for date then alter seesion nls_date_formate is good as i am using it in my query, which is shipable to any database.
is it correct?
other thing is if it is possible to do it with out alter session nls_ and without environtment setting ( like using any perticular formate, then pls tel me.
The value of this initialization parameter NLS_DATE_FORMAT is used to initialize the session value of this parameter, which is the actual value referenced by the SQL query processing. This initial value is overridden by a client-side value if the client uses the Oracle JDBC driver or if the client is OCI-based and the NLS_LANG client setting (environment variable) is defined. The initialization parameter value is, therefore, usually ignored.<<http://docs.oracle.com/cd/E11882_01/server.112/e17110/initparams145.htm>>
Proper use of TO_CHAR and TO_DATE at the individual sql statement<<http://edstevensdba.wordpress.com/category/nls_date_format/>>