1 2 Previous Next 17 Replies Latest reply: May 29, 2007 8:21 AM by -K- RSS

    Date format in SQL developer

    494547
      How do I change the default date format in SQL developer?
        • 1. Re: Date format in SQL developer
          306078
          The only way at the moment is the following SQL command:

          alter session set nls_date_format = '<format>';

          The bulk of the date display in SQL Developer is now based on this format.
          • 2. Re: Date format in SQL developer
            500233
            Hi,

            I have downloaded Linux version of SQL Developer. The exact version is 1.0.0.14.67 Build MAIN 14.67. I am wokring on Fedora Core 4, with jdk 1.5.0_06-b05.

            SQLDeveloper works fine except the following:

            When I enter

            select sysdate from dual;

            the result is one row with name sysdate (as it should be) but the content is empty. Then I tried selecting diffrent tables that contain colum of type DATE. All of them are displayed as empty fields. If I try to format the date with to_date function everything works fine.

            I started SQL Developer from command line and when I enter

            select sysdate from dual;

            the following error stack is printed out:
            lang.NullPointerException
            at oracle.dbtools.raptor.utils.NLSUtils.formatDate(NLSUtils.java:97)
            at oracle.dbtools.raptor.utils.NLSUtils.getValue(NLSUtils.java:238)
            at oracle.dbtools.raptor.controls.cellrenderers.DefaultCellRenderer.getTableCellRendererComponent(DefaultCellRenderer.java:142)
            at javax.swing.JTable.prepareRenderer(JTable.java:3924)
            at javax.swing.plaf.basic.BasicTableUI.paintCell(BasicTableUI.java:2070)
            at javax.swing.plaf.basic.BasicTableUI.paintCells(BasicTableUI.java:1972)
            at javax.swing.plaf.basic.BasicTableUI.paint(BasicTableUI.java:1895)
            at javax.swing.plaf.ComponentUI.update(ComponentUI.java:142)
            at javax.swing.JComponent.paintComponent(JComponent.java:742)
            at javax.swing.JComponent.paint(JComponent.java:1005)
            at javax.swing.JComponent.paintChildren(JComponent.java:842)
            at javax.swing.JComponent.paint(JComponent.java:1014)
            at javax.swing.JViewport.paint(JViewport.java:728)
            at javax.swing.JComponent.paintChildren(JComponent.java:842)
            at javax.swing.JComponent.paint(JComponent.java:1014)
            at javax.swing.JComponent.paintWithOffscreenBuffer(JComponent.java:4963)
            at javax.swing.JComponent.paintDoubleBuffered(JComponent.java:4916)
            at javax.swing.JComponent._paintImmediately(JComponent.java:4859)
            at javax.swing.JComponent.paintImmediately(JComponent.java:4666)
            at javax.swing.RepaintManager.paintDirtyRegions(RepaintManager.java:451)
            at javax.swing.SystemEventQueueUtilities$ComponentWorkRequest.run(SystemEventQueueUtilities.java:114)
            at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
            at java.awt.EventQueue.dispatchEvent(EventQueue.java:461)
            at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
            at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
            at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
            at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
            at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
            • 3. Re: Date format in SQL developer
              500233
              I have found the problem. Maybe it is stated somewhere but this error occurs only on Oracle 8.xxx DBs. I decompiled the NLSUtils class and I have found out the the query you were using is not working for oracle DB 8xxx.

              The query is
              select parameter,value from nls_session_parameters union all SELECT 'DB_TIMEZONE' name, DBTIMEZONE value FROM DUAL

              If you try to run this query on oracle 8xx DB error occurs. The only thing what I did is recomiling the NLSUtils class with the following query

              select parameter,value from nls_session_parameters union all SELECT 'DB_TIMEZONE' name, DBTIMEZONE value FROM DUAL
              • 4. Re: Date format in SQL developer
                Jim Smith
                Oracle 8 databases are not supported.

                I suspect it is also in violation of the license agreement to decompile the SQLDeveloper classes.
                • 5. Re: Date format in SQL developer
                  500233
                  Yep sorry, I didn't check the manual that the 8 DB are not supported.
                  • 6. Re: Date format in SQL developer
                    458726
                    If you still it will be needing, gives one looked at in:

                    Re: Alter session command on start-up
                    • 7. Re: Date format in SQL developer
                      249494
                      As per:
                      http://www.oracle.com/technology/products/database/sql_developer/howtos/howto_policy_date_formats.html

                      you have to set this each time you open a connection.

                      In sql*plus, i can edit my login.sql file.
                      Does Sql Developer have this functionality especially since it seems that it is going to replace sql*plus (windows gui anyway).

                      Thanks.
                      • 8. Re: Date format in SQL developer
                        tfa
                        Enabling the changing of "date format" specific for the sql developer sessions would be nice. I understand the session can be altered to get the desirable date format for the current session. I'm looking for something more automatic for every sql developer session I start. The prior posters reference to login.sql to set date format used by sqlplus is great. I'd like to see the preference settings expanded to enable this functionality.
                        An example of why this would be good is that I would like to view the timestamp on dates by default.
                        • 9. Re: Date format in SQL developer
                          484267
                          tfa
                          Look at the 1.1 evaluation release. You can specify a date format in it.

                          Eric
                          • 10. Re: Date format in SQL developer
                            tfa
                            Thanks! Will do, thats great news!
                            • 11. Re: Date format in SQL developer
                              564401
                              Hi!

                              I run into a similar situation.

                              When using "DD.MM.YYYY HH24:MI:SS" fields declared as "date"
                              are left blank. Table data is shown correctly, but this does not apply
                              when using selects like this one:
                              SELECT MAX(<column>) AS MaxDate FROM <table>

                              When resetting to "DD.MM.RR HH24:MI:SS" everything works fine again.

                              SQL Developer: v1.1 Patch 1 (1.1.1.25.14)
                              OS: WinXPSP2
                              Java platform: 1.5.0_11
                              • 12. Re: Date format in SQL developer
                                558660
                                Hi!

                                I run into a similar situation.

                                When using "DD.MM.YYYY HH24:MI:SS" fields declared as
                                "date"
                                are left blank. Table data is shown correctly, but
                                this does not apply
                                when using selects like this one:
                                SELECT MAX(<column>) AS MaxDate FROM <table>
                                Do they show as blank in the results pane or the script output pane?
                                What database version? There are known problems with dates when running against Oracle 8.
                                When resetting to "DD.MM.RR HH24:MI:SS" everything
                                works fine again.

                                SQL Developer: v1.1 Patch 1 (1.1.1.25.14)
                                OS: WinXPSP2
                                Java platform: 1.5.0_11
                                • 13. Re: Date format in SQL developer
                                  SueHarper
                                  Date format changes seem to be fine on 8i, 9 and 10g

                                  Sue
                                  • 14. Re: Date format in SQL developer
                                    564401
                                    Hi!

                                    Either it's fixed in 1.1.2.25 or I'm not able to reproduce it.
                                    Strange behaviour.
                                    Being able to attach screenshots would make things easier ...

                                    However. Thanx.
                                    1 2 Previous Next