If you are using this query to generate an input file for your Data Warehouse, then you need to format the data appropriately. Essentially in that case you are converting the date (which does have a time component) to a string. You need to explicitly format your string or change your nls_date_format to set the default. In your query you could simply do:

select to_char(some_date,  yyyy-mm-dd hh24:mi:ss ) my_date
  from some_table;

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.

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.

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.

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.

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

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.

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.

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.

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.

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)