8 Replies Latest reply: Jan 18, 2013 1:40 AM by 984258 RSS

    Timestamp shows only date

    984258
      Hello!
      First,- sorry for my really bad englisch,- I'll try my very best ;-)

      One Field in a table of our (very big) DWH showes a timestamp. A Query on it shows only a Date.
      But a "group by" did not work.

      Same query with toad solves the problem. Toad shows Date AND Time for the same field......

      What can i do, that SQL Developer (my favorit tool) showes hidden data like this?

      kind regards
      bernd paulus

      Edited by: user13169869 on 10.01.2013 23:04
        • 1. Re: Timestamp shows only date
          Jeff Smith Sqldev Pm-Oracle
          Check the Tools > Preferences > Database > NLS Params for Timestamp format.

          Mine is set to DD-MON-RR HH.MI.SSXFF AM
          • 2. Re: Timestamp shows only date
            Askdineshsingh
            the different behaviour of date field on different tools like Toad and Sql Developer depends on the local preferences of the tools which are configured for showing Date format / Timestamp format for respective fields.
            In case of Sql Developer you go to Tool-->Preferences-->Database-->NLS-->Timestamp Format
            In the field you type DD-MON-RRRR and restart you Sql Developer.
            Your query results will show Date as 11-JAN-2013

            If this is helpful/correct then mark it appropriately

            Edited by: Askdineshsinghminhas on Jan 11, 2013 7:47 AM
            • 3. Re: Timestamp shows only date
              rp0428
              Thanks for trying to help but the format you suggested will only dislay the date.

              Reread OPs question: it was how to also display the time.

              The responder before you answered the question.
              • 4. Re: Timestamp shows only date
                984258
                Hello and thx for your Help.
                Useful idea, but it did not solve my problem.

                My entry (German) is: DD.MM.RR HH24:MI:SSXFF

                I checked yours with restart,- same Problem......

                Any other Idea?

                /bernd
                • 5. Re: Timestamp shows only date
                  rp0428
                  Did you change all three formats to include the time?

                  And did you restart sql developer after making the changes?
                  • 6. Re: Timestamp shows only date
                    berx
                    Bernd,

                    I just set these values:
                    Date Format: YYYY-MM-DD HH24:MI:SS
                    Timestamp format: YYYY-MM-DD HH24.MI.SSXFF
                    Timestamp TZ Format: YYYY-MM-DD HH24.MI.SSXFF TZR

                    and these queries:
                    SELECT cast(SYSDATE as date) FROM dual;
                    
                    SELECT cast(sysdate AS TIMESTAMP) FROM dual;
                    
                    select cast(sysdate AS TIMESTAMP WITH LOCAL TIME ZONE) from dual;
                    gave this results:
                    CAST(SYSDATEASDATE) 
                    ---------------------
                    2013-01-16 20:21:02   
                    
                    CAST(SYSDATEASTIMESTAMP)     
                    ------------------------------
                    2013-01-16 20.21.02.000000000  
                    
                    CAST(SYSDATEASTIMESTAMPWITHLOCALTIMEZONE)
                    -----------------------------------------
                    2013-01-16 20.21.02.000000000          
                    Can you please test with these values and select statements?
                    I can imagine same (more or less unlikely) situations which also can create your described behavior without fiddling with session settings, but first let's create some easy baselines?

                    best regards,
                    Martin
                    • 7. Re: Timestamp shows only date
                      rp0428
                      >
                      SELECT cast(SYSDATE as date) FROM dual;

                      SELECT cast(sysdate AS TIMESTAMP) FROM dual;
                      >
                      SYSDATE is already a date so doesn't need to be cast.

                      And you can use SYSTIMESTAMP if you want the system timestamp. A date doesn't have the fractional seconds like a timestamp does.
                      • 8. Re: Timestamp shows only date
                        984258
                        Good Morning ;-)

                        First of all thank you guys for your help!

                        Now it works!
                        I had to change date to (German): DD.MM.RR HH24:MI:SS

                        My first edit to date (DD.MM.RR HH24:MI:SSXFF) was buggy and displays only date (without time)
                        btw,- changes work without restart

                        Thank you so much for your time and ideas.....


                        /bernd

                        Edited by: user13169869 on 17.01.2013 23:33