This content has been marked as final. Show 8 replies
Check the Tools > Preferences > Database > NLS Params for Timestamp format.1 person found this helpful
Mine is set to DD-MON-RR HH.MI.SSXFF AM
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.1 person found this helpful
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
Thanks for trying to help but the format you suggested will only dislay the date.1 person found this helpful
Reread OPs question: it was how to also display the time.
The responder before you answered the question.
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?
Did you change all three formats to include the time?
And did you restart sql developer after making the changes?
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:
gave this results:
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;
Can you please test with these values and select statements?
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
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?
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.
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.....
Edited by: user13169869 on 17.01.2013 23:33