This discussion is archived
8 Replies Latest reply: Jan 17, 2013 11:40 PM by 984258 RSS

Timestamp shows only date

984258 Newbie
Currently Being Moderated
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 ACE Moderator
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points