Oracle s default date format is YYYY-MM-DD WHY

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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] Oracle's default date format is YYYY-MM-DD, WHY?

Examples related to oracle

Examples related to date-formatting