5 Replies Latest reply: Nov 23, 2009 6:47 AM by -K- RSS

    SQL developer doesn't display time stamp

    512269
      Hi,

      SQL developer is not giving the time stamp for any query result for date columns though the column has time stamp. it is just giving data without a time. Can you please let us know if there is any setting for this.

      Also, there is no option in sql developer to ingore column names while exporting the data to text file. Can you please suggest on this settign also. I am using SQL developer 1.5.4 with main build 5940.

      Thanks
      Anand
        • 1. Re: SQL developer doesn't display time stamp
          Sentinel
          a few options for displying the time component of a date datatype exist 1) use the to_char function to convert the date to the desired textual representation, 2) alter your NLS_DATE_FORMAT for the current session, 3) change the NLS_DATE settings in the SQL Developer configuration options (in the Database branch).
          • 2. Re: SQL developer doesn't display time stamp
            628404
            Hey There,

            The fastest way in this case is to change the date format in SQLDeveloper configuration scope:

            In Oracle SQLDeveloper Go to: Tools >> Preferences >> Database > NLS Parameters and update your Date Format field to DD-MON-RR HH:MI:SS value

            From now on you will see hours minutes and seconds in your query results.

            a quick check:

            SELECT to_date('1-Jan-1970 00:00:00','dd-mon-yyyy hh24:mi:ss') + (1256130000/60/60/24) as "Date"
            FROM dual;

            Cheers!!
            • 3. Re: SQL developer doesn't display time stamp - SQLDeveloper 2.1.0.63
              736107
              setting of Date Format in NLS Settings: YYYY-MM-DD HH24:MI:SS

              date displayed in 2.1.0.63 : 2009-11-20 00:00:00
              date displayed in 1.5.4 : 2009-11-20 14:40:28

              Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

              this is not listed in the release notes Known Issues - is there any workaround?

              table definition:
              CREATE
                  TABLE "OLAPSUM"."OLAP_PROCESS_LOG"
                  (
                    "OLAPPROCESSID"      NUMBER,
                    "PARTNAME"           VARCHAR2 ( 30 CHAR ),
                    "TABLENAME"          VARCHAR2 ( 100 CHAR ),
                    "PD_GAMETYPE_ID"     NUMBER,
                    "DR_NUM"             NUMBER,
                    "PD_SUJET_SERIES_ID" NUMBER,
                    "BEGIN" DATE,
                    "END" DATE,
                    "LASTMODIFIED" TIMESTAMP ( 6 ),
                    "PROCESSEDROWS" NUMBER,
                    CONSTRAINT "OLAP_PROCESS_LOG_PK" PRIMARY KEY ( "OLAPPROCESSID", "PARTNAME", "TABLENAME", "PD_GAMETYPE_ID", "DR_NUM", "PD_SUJET_SERIES_ID" ) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "OLAPSUM_INDEX" ENABLE
                  )
                  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
                  (
                    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
                  )
                  TABLESPACE "OLAPSUM_DATA" CACHE ;
              regards

              A.

              Edited by: user12197562 on Nov 20, 2009 3:55 PM
              • 4. Re: SQL developer doesn't display time stamp - SQLDeveloper 2.1.0.63
                736333
                simply run this command in your sql work sheet you will get date with time stamp for that session

                alter session set nls_date_format='DD-MON-RR HH:MI:SS '

                Regards
                Dipanshu
                cheers!