Oracle's default date format is YYYY-MM-DD, WHY?

Aaron Fi picture Aaron Fi · Dec 3, 2009 · Viewed 436.4k times · Source

Oracle's default date format is YYYY-MM-DD. Which means if I do:

 select some_date from some_table

...I lose the time portion of my date.

Yes, I know you can "fix" this with:

 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

But seriously, why isn't the above the default? Especially in a DBMS where the two primary time-tracking data types (DATE and TIMESTAMP) both have a time component that includes (at least) accuracy down to 1 second.

Answer

skaffman picture skaffman · Dec 3, 2009

Are you sure you're not confusing Oracle database with Oracle SQL Developer?

The database itself has no date format, the date comes out of the database in raw form. It's up to the client software to render it, and SQL Developer does use YYYY-MM-DD as its default format, which is next to useless, I agree.

edit: As was commented below, SQL Developer can be reconfigured to display DATE values properly, it just has bad defaults.