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.
This question is related to
oracle
date-formatting
It's never wise to rely on defaults being set to a particular value, IMHO, whether it's for date formats, currency formats, optimiser modes or whatever. You should always set the value of date format that you need, in the server, the client, or the application.
In particular, never rely on defaults when converting date or numeric data types for display purposes, because a single change to the database can break your application. Always use an explicit conversion format. For years I worked on Oracle systems where the out of the box default date display format was MM/DD/RR, which drove me nuts but at least forced me to always use an explicit conversion.
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.
A DATE value per the SQL standard is YYYY-MM-DD. So even though Oracle stores the time information, my guess is that they're displaying the value in a way that is compatible with the SQL standard. In the standard, there is the TIMESTAMP data type that includes date and time info.
To answer to your question that is WHY default date don't display TIME part, the only answer I find is
Oracle teams are composed of LAZY developpers or responsibles :-)
Why ?
Because DATE, TIME and DATETIME datatypes exist in SQL and Oracle has not yet implemented it !!!
It is a shame for Oracle.
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.
reason: if you are looking at a column of data with a time stamp, the _MOST_IMPORTANT_ bit of information is the year. If data has been in the db for ten years, that's important to know. so year comes first.
it makes sense that month would come next, then day, hour, minute. Objectively, these are the most logical sequence for time data to be displayed.
it also makes sense that if you are going to display the data by default, you should display only the most significant portion of the data, so by default, only Y-M-D. everything else CAN be displayed, but it does not clutter your sql report by default.
Ordering by date is logical if you display Y-M-D because it is sequential. Computers are good at sequential, and it looks logical.
finally. Your bias to want M-D-Y is your bias. Not everyone even in the US uses this format. So use the most logical format and don't be outraged when others decide to be logical by default.
(I am US born, and I do not represent Oracle. I can, however, think for myself)
The biggest PITA of Oracle is that is does not have a default date format!
In your installation of Oracle the combination of Locales and install options has picked (the very sensible!) YYYY-MM-DD as the format for outputting dates. Another installation could have picked "DD/MM/YYYY" or "YYYY/DD/MM".
If you want your SQL to be portable to another Oracle site I would recommend you always wrap a TO_CHAR(datecol,'YYYY-MM-DD') or similar function around each date column your SQL or alternativly set the defualt format immediatly after you connect with
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
or similar.
I'm not an Oracle user (well, lately anyhow), BUT...
In most databases (and in precise language), a date doesn't include a time. Having a date doesn't imply that you are denoting a specific second on that date. Generally if you want a time as well as a date, that's called a timestamp.
Most of the IDE you can configure the default mask for some kind of data as date, currency, decimal separator, etc.
If your are using Oracle SQL Developer:
Tool > Preferences > Database > NLS
Date Format: YYYY-MM-DD HH24:MI:SS
Oracle has both the Date and the Timestamp data types.
According to Oracle documentation, there are differences in data size between Date and Timestamp, so when the intention is to have a Date only field it makes sense to show the Date formatting. Also, "It does not have fractional seconds or a time zone." - so it is not the best choice when timestamp information is required.
The Date field can be easily formatted to show the time component in the Oracle SQL Developer - Date query ran in PL/SQL Developer shows time, but does not show in Oracle SQL Developer. But it won't show the fractional seconds or the time zone - for this you need Timestamp data type.
The format YYYY-MM-DD is part of ISO8601 a standard for the exchange of date (and time) information.
It's very brave of Oracle to adopt an ISO standard like this, but at the same time, strange they didn't go all the way.
In general people resist anything different, but there are many good International reasons for it.
I know I'm saying revolutionary things, but we should all embrace ISO standards, even it we do it a bit at a time.
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.
Source: Stackoverflow.com