5 Replies Latest reply on Feb 18, 2013 7:21 AM by asahide

    a universal date time  formate for oracel.


      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.
          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;
          Typ=12 Len=7: 120,113,2,16,8,41,10
          Here's how I choose to display it.
          SQL> SELECT * FROM demo;
          16-FEB-2013 07:40:09
          Here is how I made that choice in my glogin.sql file.
          A full listing of possible format options can be found here:
          • 2. Re: a universal date time  formate for oracel.
            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.

              You can set NLS_DATE_FORMAT in your environment variable.

              • 4. Re: a universal date time  formate for oracel.
                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.

                  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.

                  Proper use of TO_CHAR and TO_DATE at the individual sql statement