This discussion is archived
5 Replies Latest reply: Feb 17, 2013 11:21 PM by asahide RSS

a universal date time  formate for oracel.

947771 Newbie
Currently Being Moderated
Hi,

I was working on mssql , we where using a formate 01 Dec 2012 or iso ,
and checked that after changing the OS date formate also this was working properly
whether PL,BALand DAL is kept on one machine and database is kept on other machine. or not.

we also tested it using datetime type variable in DAL and called stored procedure. to some task
it was working in all condition

for oracle please tel me which formate (for date and time together) is universal and will work even if i am using string type variable to pass date date from DAL to oracel server, in insert query.


yours sincerely
  • 1. Re: a universal date time  formate for oracel.
    damorgan Oracle ACE Director
    Currently Being Moderated
    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.
    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's how I choose to display it.
    SQL> SELECT * FROM demo;
    
    MYCOL
    --------------------
    16-FEB-2013 07:40:09
    Here is how I made that choice in my glogin.sql file.
    ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
    A full listing of possible format options can be found here:
    http://www.morganslibrary.org/reference/convert_func.html#ctch
    http://www.morganslibrary.org/reference/convert_func.html#ctdt
  • 2. Re: a universal date time  formate for oracel.
    947771 Newbie
    Currently Being Moderated
    is it possible to get the solution of problem as stated initially with out using , alter session nls_ ?

    yours sincerely
  • 3. Re: a universal date time  formate for oracel.
    asahide Expert
    Currently Being Moderated
    Hi,

    You can set NLS_DATE_FORMAT in your environment variable.

    Regards,
  • 4. Re: a universal date time  formate for oracel.
    947771 Newbie
    Currently Being Moderated
    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.

    yours sincerlly
  • 5. Re: a universal date time  formate for oracel.
    asahide Expert
    Currently Being Moderated
    Hello,

    Manual said..
    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>>

    So...
    Proper use of TO_CHAR and TO_DATE at the individual sql statement
    <<http://edstevensdba.wordpress.com/category/nls_date_format/>>

    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points