How to show or display date time values in Oracle SQL Developer

Those who use the Oracle SQL Developer for running the queries will face the problem of displaying the time. When you run a select statement for a table which has the data and time column, the result would show only the date and not the time. By setting the alter statement for the session you use, you can see the date and time values. This alter statement has to be set for each sessions.

Display or show the date and time values

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’

In Category: Technical

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 7 Comments
  • Rohan October 4, 2010, 1:05 pm

    Good one

  • Barf February 19, 2011, 1:16 am

    You can also just set it permanently in tools -> preferences -> NL settings.

  • Steve March 17, 2011, 4:44 am

    Great post, and find,
    Thanks OP, and Barf

  • Sujeeth September 14, 2011, 6:06 pm

    Enjoy this –
    select to_char(column_name, ‘dd/mm/yyyy hh24:mi:ss’) from table name

  • shailendra November 3, 2011, 2:24 pm

    getting error while using first one on using top of the query….

  • shailendra November 3, 2011, 2:26 pm

    getting error 00911invalid character

  • shailendra November 3, 2011, 2:27 pm

    second option i have also tried but does not work for language English

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.