Changing Oracle’s Default Date Format

If you are stuck using Oracle’s SQL Developer like I am (Toad is $800 now!), then don’t suffer with the braindead default date format like I have until recently.  There’s nothing like just seeing the date in dd-mmm-yy format when you are working with hourly or sub-hourly data all the time like I am.  What were they thinking?  Anyway, you can run this once in your SQL worksheet and enter and see dates with times:

alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI';


2 Responses to “Changing Oracle’s Default Date Format”

  1. Bill Holmes Says:

    A more flexible way is to apply the to_char transformation to a date datatype column, e.g.:

    select to_char(datedatatypecol, ‘MM/DD/YYYY HH24:MI:SS) from tablename;

    The format mask can differ quite a bit. The default format is DD-MON-YY: select sysdate from dual; produces:


  2. Bill Says:

    Thanks. This was driving me crazy. However, after reading your post, I reviewed the preferences, and perhaps this was added since your post, but I did find that this can now be set in the tool (version 1.5.1), under;
    Preferences->Database->NLS Parameters.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: