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

Asked 3/12, 2009 at 6:7 Answered 3/8, 2023 at 9:7

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.

Helminth answered 3/12, 2009 at 6:7 Comment(12)

please don't close; i would really welcome an explanation / rationalization from some experienced member of the Oracle community. – Helminth 3/12, 2009 at 6:10

The precision is not lost - it just isn't displayed. Each db has it's quirks - this one is the least of my concerns. IMO: SQL Server's lack of implicit data type conversion is a bigger PITA. – Hardfeatured 3/12, 2009 at 6:15

The precision is definitely lost when you're using the displayed results as the input file into a Data Warehouse. : ( – Helminth 3/12, 2009 at 6:19

Aaron, one should never use "select *" for any purpose but just observing data. Suppose number of columns is changed, or NLS settings is changed -- what then? Use explicit list of columns with necessary conversions, format masks etc. – Sunrise 3/12, 2009 at 6:28

Wow, this is like complaining about the default seat position in a purchased car... "the default position is all the way back, oh dear god, WHY???"... just change it once to what you want and leave it! :) – Peder 3/12, 2009 at 9:3

This is actually a very real problem! Connecting to ORACLE and selecting a Date column could return almost anything. There were two ways to go with this DB2 said we have a defult date format its 'YYYY-MM-DD' you can use a function to re-format it if you want. Oracle said lets let the sysadmin choose the default date format on install. Dates, Times and Timestamps are the biggest problem when porting apps (or data) from one database to another. Especially from Oracle you really need to examine usage to find out of the date is really a date/time. – Edgerton 3/12, 2009 at 10:59

This seems more like a "whine" than a "rant". ALTER SYSTEM and you have your own default, forever. – Alake 3/12, 2009 at 13:56

Selecting a date from oracle through OCI returns a seven byte value. It is the responsibility of the CLIENT SOFTWARE to translate that into a character string. In the case of ODBC/JDBC, the driver maps the seven byte value in the appropriate local datatype. – Godavari 3/12, 2009 at 22:35

For the benefit of those who visit this question on or after the day this comment is posted: the default date format in oracle now is "DD-MON-YY" – Pavonine 28/10, 2013 at 0:43

I disagree with ones saying this is "whining" or "just like a default seat position in a car". This is like a car that comes with its driver seat turned 90º sideways and people have to ask help on how to put it right. I mean, it is nice to be able to change the format, but it is just sensible to expect the default format to be ISO instead of 'DD-MON-YY'. – Lott 6/9, 2017 at 20:16

@R.S.K The default date format is not that simple as it depends on the NLS_TERRITORY session/database parameter so setting up servers in different locations around the world (using their local settings) can have different defaults. The Philippines has MM/DD/RRRR while Israel is DD-MON-RRRR and Sweden is RRRR-MM-DD so TO_DATE( '01-02-03' ) would give different results in each of those territories. – Windowpane 1/6, 2018 at 11:40

Oracle's default date format is certainly not YYYY-MM-DD (I wish it was), so the question is misguided from the start, although it's true that the various locale-specific defaults don't include the time. If this was a new post I'd vote to close as opinion-based and not a programming question. – Microbarograph 21/6, 2018 at 8:2

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;

Ingrown answered 3/12, 2009 at 14:3 Comment(0)

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.

Serin answered 3/12, 2009 at 8:4 Comment(3)

In SQL Developer, Tools > Preferences > Database > NLS Parameters ... there are thirteen parameters that can be set for controlling defaults. – Jeffcott 3/12, 2009 at 9:55

Fair enough, it can be configured, but why the downvote? My point still stands that this is an aspect of the client software, not the database, which was the point of the question. – Serin 3/12, 2009 at 9:59

I think that the principle of not relying on defaults is universal and not tool dependent, so i didn;t think it was very relevant. – Jeffcott 3/12, 2009 at 17:15

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

Ingrown answered 3/12, 2009 at 14:3 Comment(0)

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.

Hierophant answered 3/12, 2009 at 6:34 Comment(0)

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.

Edgerton answered 3/12, 2009 at 6:34 Comment(3)

The DBA can set up whatever NLS_DATE_FORMAT system-wide that they want. However, often the database simply returns a value in the seven byte internal date format, and the client software does its own job of translating that into a string. – Godavari 3/12, 2009 at 22:38

If Oracle was to have a hard-coded default date format, please tell me - which one should it be? AND how do you suggest explaining this to all the people out there in the world who want it to be something else? (There's always someone who's not happy with whatever decision is made!) Oracle doesn't have a hard-coded default date format because Oracle is a commercial product which tries to satisfy the requirements of all users. – Spectroradiometer 30/4, 2018 at 17:49

The lack of default/interal date format is indeed a cause of problems as developers assume whatever format is on their system is a universal format for all Oracle based systems. Even systems within the same data centre can have different defaults. I have seen many problems caused by this lack of standardisation. – Edgerton 2/5, 2018 at 6:11

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.

Jeffcott answered 3/12, 2009 at 7:47 Comment(0)

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

Ecclesiasticus answered 2/9, 2014 at 12:37 Comment(0)

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.

Deterge answered 3/12, 2009 at 6:31 Comment(0)

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.

Soggy answered 6/1, 2011 at 0:14 Comment(0)

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.

Chiarra answered 3/12, 2009 at 6:14 Comment(3)

Nick, thanks for the reply. I agree with you conceptually. It seems Oracle's two types, DATE vs TIMESTAMP, both have a time component. It's just that the TIMESTAMP goes down to more granularity (sub 1-second). it.toolbox.com/blogs/database-solutions/… You would think that the inclusion of a time component in both types would be reason to not have a YYYY-MM-DD display format. – Helminth 3/12, 2009 at 6:19

@Nick: Oracle's DATE type has a one-second precision. It's badly named. – Serin 3/12, 2009 at 8:4

@skaffman: It apparently isn't that badly named if they have the default string format set to only include the date portion and separately have a higher resolution TIMESTAMP data type. Of course I don't know what their actual recommendation is, but that seems a pretty clear distinction to me on proposed usage. (As always, of course, things with databases are never so clear...) – Chiarra 3/12, 2009 at 8:40

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.

Civility answered 27/3, 2013 at 9:31 Comment(0)

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)

Festival answered 30/12, 2014 at 19:9 Comment(0)

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

The question is based on a common misconception that a DATE data-type has a format in the database; it does not.

In Oracle, a DATE is a binary data-type consisting of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 components and it is NEVER stored in any particular human-readable format.

Why those 7 components and not just year, month and day like an ANSI-standard DATE?

Oracle's DATE data type predates the ANSI standard (by about 7 years) and has maintained backwards compatibility with previous database versions.

Why does Oracle's default date format appear to be YYYY-MM-DD?

As stated, it is not, it is a 7-byte binary value without a format. When the client application (i.e. SQL*Plus, SQL Developer, Java, C#, etc.) requests a DATE then the database will send that client the 7-byte DATE value; however, unformatted binary data is not particularly useful to you, the user, so the client application chooses how to format the date so that it is meaningful to you, the user, and that means it has to convert it from binary to a formatted value.

This means that any implicit formatting of a DATE is done by the client application and NOT by the database. The database will ALWAYS work with the raw bytes when it is handling dates.

Why does the client application's default date format appear to be YYYY-MM-DD?

This depends on the client application you are using. However, in most cases, you can change the preferences for the client application and set your own format model for DATEs. You will need to refer to the documentation for that client application (as there are many different clients and they will all have a different way to set the default DATE format).

Let us assume that you are using an Oracle client application (such as SQL*Plus, SQL Developer or SQLCl), why does the client application's default date format appear to be YYYY-MM-DD?

This is not entirely true.

Those clients do not use YYYY-MM-DD as the format model but instead default to use the database's NLS_DATE_FORMAT as the client's format model for displaying dates (although they can be overridden with in the client-side configuration).
The database's NLS_DATE_FORMAT is the format that Oracle will use to implicitly cast strings-to-dates and dates-to-strings (i.e. if you use TO_DATE(string_column) or TO_CHAR(date_column) without specifying a format model in the second argument then it will use the NLS_DATE_FORMAT). However, it is worth repeating that when a client application requests a DATE then the database does not perform any modifications to the binary data it holds and just sends the client the 7-byte value and lets the client application do whatever it wants with the entire DATE (including the time component).
The NLS_DATE_FORMAT is based on the NLS_TERRITORY parameter and therefore the default NLS_DATE_FORMAT varies throughout the world; out-of-the-box, a database instance only has the format RRRR-MM-DD when the territory is set to Bulgaria or Sweden (however, the DBA can override the defaults during database creation).
So it appears that either: you are in Bulgaria or Sweden and the default is that because Oracle has determined that the most prevalently used date format in your country is RRRR-MM-DD and set that as the default for your territory; or that your DBA has set the default to YYYY-MM-DD (possibly because it is the ISO8601 standard for formatting a date).

So, the answer to your question is:

You appear to be using a client application that implicitly applies a format to dates based on the database NLS_DATE_FORMAT session parameter and the NLS_DATE_FORMAT was set either implicitly based on the NLS_TERRITORY chosen during database creation or explicitly by the DBA.
If you want to change how the client application formats a DATE then change the default settings (either on the client or, if the client takes the default from the database then change the database).

Windowpane answered 3/8, 2023 at 9:7 Comment(0)

-4

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.

Unwind answered 21/6, 2018 at 7:31 Comment(1)

If you will downvote, please give some explanation ? Example: your work at Oracle or you have some Oracle actions :-) – Unwind 21/6, 2018 at 9:12

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags