5

I have a table named AUDIT which saves all the modifications done on an account. There is a field AUDIT_DATE which contains the date on which a particular modification has been done. Now the problem is, in one of my colleagues Oracle PL/SQL developer, I can see the time as well as the date in the AUDIT_DATE column, but in my Oracle PL/SQL developer, I can't see the time part. So I believe some setting is not properly done in my Oracle PL/SQL developer and that's why I can't see the time part. I asked the colleague, but he has no clue about the discrepancy.

Mistu4u
  • 5,132
  • 15
  • 53
  • 91
  • 2
    Oracle SQL Developer as you've tagged it, or Allround Automation's PL/SQL Developer? They are distinct things and you seem to be confusing them in the question... – Alex Poole May 16 '14 at 18:15
  • @AlexPoole ITs Oracle Pl/SQL developer. – Mistu4u May 17 '14 at 15:37
  • 1
    Oracle don't make a product called "PL/SQL Developer". [They do make one called "SQL Developer"](http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html). But you might mean [this](http://www.allroundautomations.com/plsqldev.html). I've given the info for both in my answer though. – Alex Poole May 17 '14 at 16:41

1 Answers1

16

If you want to see dates with times shown as well without applying a format mask with to_char(), you need to change your NLS_DATE_FORMAT. Assuming you do mean Oracle SQL Developer, you can do this from Tools->Preferences, expand the Database section in the panel on the left, and select NLS:

enter image description here

At the moment NLS_DATE_FORMAT is set to DD-MON-RR, which would show today as 16-MAY-14. To show the full date and time I might set it to YYYY-MM-DD HH24:MI:SS. You might want to change the NLS_TIMESTAMP format as well.

PL/SQL Developer also has NLS options under Tools->Preferences:

enter image description here

You can see the available format models in the documentation.

If you're writing code that will or may ever be executed by someone else, do not rely on implicit formatting using those parameters. They're fine for ad hoc queries in your own enclosed environment, but they may break in interesting ways when someone else - with different NLS settings - runs them. For anything except ad hoc queries you should really specify the mask, using to_char(<column>, 'YYYY-MM-DD HH24:MI:SS') or whatever model is appropriate. This of course also means you get the right formatting for the column; if you have columns that only represent times then setting the session's format model and relying on that means you see all the 00:00:00 times, which is often just noise.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318