1 Reply Latest reply: Sep 6, 2012 12:50 PM by Brian Bontrager RSS

    date format on Oracle 11g

    GoldSoft
      hi
      i see my dates in Oracle 11g like this: *2012-December-30* (in all my tables)
      how to configure Oracle that i can see it like this: *30/12/2012*

      thank in advance
        • 1. Re: date format on Oracle 11g
          Brian Bontrager
          Update your NLS_DATE_FORMAT to "DD/MM/YYYY".

          http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/tdddg_globalization.htm#TDDDG62410

          Depending which application you are using to query the data, it may be over-ridden for your session depending on application's options. For example, the format you see in sqlplus may be different than the format you see in TOAD or SQL Developer (or any client application).


          In sqlplus:
          SQL> SHOW PARAMETER NLS_DATE_FORMAT
          
          NAME                                 TYPE                             VALUE
          ------------------------------------ -------------------------------- ------------------------------
          nls_date_format                      string                           DD-MON-YYYY
          SQL> SELECT SYSDATE FROM DUAL;
          
          SYSDATE
          ---------
          06-SEP-12
          In SQL Developer, in Tools/Preferences/Database/NLS my Date Format is "DD-MON-YYYY HH24:MI:SS"

          Running the same queries in SQL Developer displays:
          NAME                                 TYPE                             VALUE
          ------------------------------------ -------------------------------- ------------------------------
          nls_date_format                      string                           DD-MON-YYYY                                                                                                                                                                                                                                                                                                                                         
          SYSDATE              
          ----------------------
          06-SEP-2012 13:41:18   
          EDIT:
          The above sets the overall default for your session. If you need a specific SQL query to always return a certain date format, use TO_CHAR() to specify the exact format you want for that result.

          Edited by: Brian Bontrager on Sep 6, 2012 1:48 PM