How can I set a custom date time format in Oracle SQL Developer

Question

By default  Oracle SQL developer displays date values as 15-NOV-11  I would like to see the time part  hour minute second  by default    Is there a way to configure this within Oracle SQL Developer

User · Answer

In my case the format set in Preferences/Database/NLS was [Date Format] = RRRR-MM-DD HH24:MI:SSXFF but in grid there were seen 8probably default format RRRR/MM/DD (even without time) The format has changed after changing the setting [Date Format] to: RRRR-MM-DD HH24:MI:SS (without 'XFF' at the end).

There were no errors, but format with xff at the end didn't work.

Note: in polish notation RRRR means YYYY

User · Answer

When i copied the date format for timestamp and used that for date  it did not work  But changing the date format to this  DD-MON-YY HH12 MI SS AM  worked for me     The change has to be made in Tools- Preferences-  search for NLS

User · Answer

With Oracle SQL Developer 3 2 20 09  i managed to set the custom format for the type DATE this way    In   Tools   Preferences   Database   NLS  Or   Outils   Pr  f  rences   Base de don  es   NLS     YYYY-MM-DD HH24 MI SS     Note that the following format does not worked for me       DD-MON-RR HH24 MI SS   As a result  it keeps the default format  without any error

User · Answer

SQL Developer Version 4 1 0 19  Step 1  Go to Tools -  Preferences  Step 2  Select Database -  NLS  Step 3  Go to Date Format and Enter    DD-MON-RR HH24  MI  SS  Step 4  Click OK

User · Answer

For anyone still having an issue with this  I happened to find this page from Google     Put it in like this  NLS Parameters     it sticks for me in SQLDeveloper v3 0 04   DD-MON-YY HH12 MI SS AM or for 24-Hour  DD-MON-YY HH24 MI SS

User · Answer

You can change this in preferences    From Oracle SQL Developer s menu go to  Tools   Preferences  From the Preferences dialog  select Database   NLS from the left panel  From the list of NLS parameters  enter DD-MON-RR HH24 MI SS into the Date Format field  Save and close the dialog  done    Here is a screenshot

User · Answer

Goto to Tools   Preferences  In the tree  select Database   NLS  There are three Date Time formats available  Date  Timestamp and Timestamp TZ  Editing the Date format gives the desired effect   Like I have said above  this approach has not given me a permanent change

User · Answer

I have a related issue which I solved and wanted to let folks know about my solution  Using SQL Developer I exported from one database to csv  then tried to import it into another database  I kept getting an error in my date fields  My date fields were in the Timestamp format    28-JAN-11 03 25 11 000000000 PM   The above solution  changing the NLS preferences  did not work for me when I imported  but I finally got the following to work   In the Import Wizard Column Definition screen  I entered  DD-MON-RR HH MI SSXFF AM  in the Format box  and it finally imported successfully  Unfortunately I have dozens of date fields and to my knowledge there is no way to systematically apply this format to all date fields so I had to do it manually    sigh  If anyone knows a better way I d be happy to hear it

User · Answer

I stumbled on this post while trying to change the display format for dates in sql-developer  Just wanted to add to this what I found out    To Change the default display format  I would use the steps provided by ousoo i e Tools   Preferences       But a lot of times  I just want to retain the DEFAULT FORMAT while modifying the format only during a bunch of related queries  That s when I would change the format of the session with the following   alter SESSION  set NLS DATE FORMAT    my required date format    Eg      alter SESSION set NLS DATE FORMAT    DD-MM-YYYY HH24 MI SS

[oracle] How can I set a custom date time format in Oracle SQL Developer?

Examples related to oracle

Examples related to oracle-sqldeveloper