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

user_39529 0
Viewed 441523

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.

date-formatting oracle
12 Answers

This is a "problem" on the client side, not really an Oracle problem.

It's the client application which formats and displays the date this way.

In your case it's SQL*Plus which does this formatting.
Other SQL clients have other defaults.

user_3303150

Because Oracle has not yet implemented DateTime or SmallDateTime as Microsoft Sql Server !

But the correct answer to your problem is not to define a FIX default format but a SIGNIFICANT default format that display only significant digits so that DATE, TIME or DATETIME values displayed (by default) contains always all important digits.

Example:

2015-10-14          will be displayed as 2015-10-14 (or default DATE format)
2018-10-25 12:20:00 will be displayed as 2018-10-25 12:20
1994-04-16 16       will be displayed as 1994-04-16 16

The principle is simple.

All digits being part of DATE will always be displayed as INTEGER part of float number. For TIME part, only significant part will be displayed as for DECIMAL part in float number.

Naturally, for TIME type (only HH:MM:SS), the DATE part is never displayed.

To display DATE+TIME on Oracle your must define a DATE type; but this is not a SQL standard.

user_9483590
Related