[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


How to change default date format in SQL Developer

Published on
13,201 Points
2 Endorsements
Last Modified:
If you are anything like me, everytime you get a new computer or need to do a fresh install of your work computer you immediatly go and re-install SQL Developer on your machine.  You get all your connections setup and you think you are good to go.  Then you try to write your first query. 
Select * from my_table where date_column > '2010-05-05'. 

Open in new window

You run the query and you get an error message that the date is in the wrong format.   SQL Developer comes preset with a date format that it wants to use, and it is never the one I want.

Now to fix this you can always use the to_date function provided in pl/sql, but if you are writing a lot of queries that use dates this can become annoying.  It is nice to be able to just plugin what ever date format you always use and have SQL Developer remember this syntax.  The good news is you can do this in SQL Developer.  I always have a hard time finding the exact setting, so here is exactly how you would do it.

In the top menu go to the Tools -> Preferences -> Database -> NLS

Within the NL set the Date Format, Timestamp Format and the Timestamp TZ Format.  Being from the United States, below are the values I like to use.

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

Once you hit 'OK', your settings will now be updated.  Now you will be able to write queries with specified dates in the format you wanted to use.  Now you run your query from above again.
Select * from my_table where date_column > '2010-05-05'.  

Open in new window

With the above settings you get the results you want and you are back to work.

Expert Comment

by:santosh shetye
     but how to do this using query (manually) in sql developer...

Expert Comment

by:ajinkya kaspale
same doubt like #santoshshetye336

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month