Great question—this confusion boils down to one critical truth: Oracle doesn’t have a single, universal default date format—it’s highly configurable across multiple layers, which is why you’re seeing all those conflicting claims online. Let’s break it down clearly:

• Database-level configuration: The core default is controlled by the NLS_DATE_FORMAT parameter, which your DBA can set when setting up or modifying the database instance. Some teams stick with Oracle’s classic legacy default DD-MON-YY (the one you ran into), while others opt for YYYYMMDD for compact storage, DD/MM/YYYY to match regional date norms, or YYYY-MM-DD for alignment with SQL standards. Every database can have its own unique default.

• Session-level overrides: Any user can tweak the date format for their active session with a simple command:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
  

  This means even on the same database, two different users could be working with totally different default date formats at the exact same time.

• Client-side influences: Tools like SQL*Plus, SQL Developer, or third-party clients might respect your machine’s NLS_LANG environment variable, which can override the database’s default for display purposes. Some tools even auto-convert dates to a user-friendly format behind the scenes, adding another layer of mixed signals.

In your case, the DD-MON-YY format you uncovered using TO_DATE() is just the default configured for your specific database instance (or your current session). The other formats you saw online are all valid defaults that other teams/organizations use in their own Oracle environments—none of them are "wrong," they’re just context-dependent.

Pro Tip #

To avoid this kind of confusion entirely, never rely on the default date format in your code. Always explicitly define the format with TO_DATE():

INSERT INTO my_table (date_col) VALUES (TO_DATE('25-01-2018', 'DD-MM-YYYY'));

Or use ANSI-standard date literals, which are completely format-agnostic:

INSERT INTO my_table (date_col) VALUES (DATE '2018-01-25');

内容的提问来源于stack exchange，提问作者BigRedEO