Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the internal date format, which I need to get down to the second (ie 'mm/dd/yyyy hh12:mi:ss'). I do NOT want to change the query of the output format, I want to change the stored value to be updated.

Question: Is it even possible to get the database precision down to the second? If so how, I assume its an update or alter command?

1 Answer 1


I have an oracle database table with date format as 'mm/dd/yyyy'

No you don't.. ;)

You have an Oracle database table with a date datatype. Period.

Any type of "format" is for display only, and is easily modified.

  SQL> create table junk ( id number,  dd  date );

  Table created.

  SQL> insert into junk values ( 1, sysdate );

  1 row created.

  SQL> commit;

  Commit complete.

  SQL> select * from junk;

          ID DD
  ---------- ---------
           1 31-MAY-16

  SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
    2  /

  Session altered.

  SQL> select * from junk;

          ID DD
  ---------- --------------------
           1 31-may-2016 11:17:44


An Oracle DATE datatype holds Year + Month + Day + Hours + Minutes + Seconds information. It's up to you how to display/use it.

Note that an Oracle TIMESTAMP datatype holds milliseconds in addition to what a DATE holds.

It would be much easier to give you a more specific answer if you'd actually show your table definition, and your query and explain the problem you're having, and what you want to see.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.