Oracle'S Default Date Format

Oracle's default DATE format

Oracle, as well as other databases, allows you to set the default format. Out of the box, the format is (typically) DD-MON-RR, where "RR" refers to a two-digit year. This is a pretty lousy format, from the perspective of ambiguity (two digit year?) and internationalization (for what countries is that actually the default?). But Oracle has been around a long, long time.

Standard formats are also defined by ISO, the International Standards Organization. They settled on something more like YYYY-MM-DD. Actually, the hyphens are optional, but I think they make the date much more readable.

Oracle accepts constants in this format, if you use DATE:

select DATE '2018-01-25'

This is very handy. First, it is nice to support reasonable standards. Second, the code is safe, regardless of internationalization settings. Oracle documentation of course covers this in detail; here is one place to start.

What is Oracle's Default Date Format?

Don't do that - you are relying on implicit data type conversion which is going to fail at some point.

You have two options:

1) Use a proper ANSI SQL date literal:

select * 
from search
where search_date >= timestamp '2016-03-16 00:00:00';

2) use to_date() (or to_timestamp()) and use a custom format.

select * 
from search
where search_date >= to_date('03/16/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss');

With to_date() you should avoid any format that is language dependent. Use numbers for the month, not abbreviations (e.g. 'Mar' or 'Apr') because they again rely on the client language.

More details can be found in the manual:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF51062


Never rely on implicit data type conversion.

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

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;

What is the Default Date Format in Oracle?

If you do not know the default date format of your database, you should use a mask and the to_date() function, like:

insert into emp_company values ('ANIL','ACC',1500.00,to_date('01-05-89', 'DD-MM-RR'));

Also, I'm not sure this is always the case, but I've never encountered an Oracle database that did not use the 'DD-MON-YY' as the default date format (so '01-MAY-89').

How can I set a custom date time format in Oracle SQL Developer?

You can change this in preferences:

  1. From Oracle SQL Developer's menu go to: Tools > Preferences.
  2. From the Preferences dialog, select Database > NLS from the left panel.
  3. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field.
  4. Save and close the dialog, done!

Here is a screenshot:

Changing Date Format preferences in Oracle SQL Developer

Date format in oracle PL/SQL

In Oracle, a DATE is a binary data type consisting of 7 bytes representing each of

  1. century;
  2. year-of-century;
  3. month;
  4. day;
  5. hour;
  6. minute; and
  7. second

It ALWAYS has those components and it is NEVER stored in any particular format.



How to check the date format for a specific column in a table in oracle?

It does not have any format so you cannot.

If you want to see the binary values of a DATE you can use the DUMP function:

SELECT DUMP(date_column) FROM table_name;

Does oracle DATE datatype have the ability to store both formats (US and european)?

No, it never stores any format.

If you want to store formatted data then use a string data-type; however, this is generally considered to be bad practice for dates as it introduces ambiguity between days and months if you use both DD/MM/YYYY and MM/DD/YYYY. Best practice would be to store dates as a DATE and then pick the appropriate format when you DISPLAY the date.


If you want to DISPLAY a DATE date type then you can use the TO_CHAR function and specify the format model appropriate to the territory you want to display it as.

SELECT TO_CHAR(date_column, 'DD/MM/YYYY HH24:MI:SS') AS eu_format,
TO_CHAR(date_column, 'MM/DD/YYYY HH24:MI:SS') AS us_format,
TO_CHAR(date_column, 'YYYY-MM-DD"T"HH24:MI:SS') AS iso8601_format
FROM table_name

db<>fiddle here

The default format model (as used by the SQL engine when implicitly converting strings-to-dates, and vice-versa, or used by some client applications to DISPLAY a date) for different territories is listed in this answer.

Oracle date format while using TO_CHAR()

Lets see what Oracle actually does when you insert into a date column using various formats: ISO Standard, Oracle's NLS_DATE_FORMAT specification, and a format I just made up. Then a couple queries, and finally, with the DUMP function, a peek inside. (Also see here for slightly different set.)

create table date_examples( date_1 date, date_2 date, date_3 date);

alter session set nls_date_format = 'dd-Mon-yyyy'; -- set default

-- set the same date in verious formats: ISO Standare, Declared Standard, a strange format
insert into date_examples( date_1, date_2, date_3)
select date '2021-02-18' -- standard iso format
, to_date('18-Feb-2021') -- defaulr see alter session
, to_date('18 2021 02', 'dd yyyy mm') -- specified
from dual;

-- what are the various dates without specifying how
select * from date_examples;

-- now change the default
alter session set nls_date_format = 'Month dd, yyyy hh24:mi:ss'; -- set default
select * from date_examples;

-- take a peek at the inside.
select dump(date_1), dump(date_2), dump(date_3)
from date_examples;

Oracle 19c zero hour default date format with time without using any formating function

without using any formatting function

That would be a really bad idea. It means that you're about to insert a string into a DATE datatype column, hoping that Oracle will "recognize" that string, implicitly convert it to a valid DATE datatype value and insert it into a column. There are just too many "what if"s that might go wrong and - sooner or later - some of them will.

Sample table:

SQL> create table test (id number, datum date);

Table created.

This is your query:

SQL> insert into test (id, datum) values (1, '4/Jan/21 00:20:00 AM');
insert into test (id, datum) values (1, '4/Jan/21 00:20:00 AM')
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

I'll apply to_date to it, using appropriate format model (yes, I know, you don't want that - just see what happens):

SQL> insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am'));
insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am'))
*
ERROR at line 1:
ORA-01843: not a valid month

How come "Jan" is not a valid month? Well, it is not. In Croatia (and that's the language my database speaks), it is "Sij". But OK, I can add additional parameter to TO_DATE:

SQL> insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am', 'nls_date_language = english'));
insert into test (id, datum) values (1, to_date('4/Jan/21 00:20:00 AM', 'dd/Mon/yy hh:mi:ss am', 'nls_date_language = english'))
*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12

As Zakaria commented, 00 is an invalid value; change it to 12:

SQL> insert into test (id, datum) values (1, to_date('4/Jan/21 12:20:00 AM', 'dd/Mon/yy hh:mi:ss am', 'nls_date_language = english'));

1 row created.

Not it succeeded. What did I insert? Right, that's 20 minutes past midnight.

SQL> select id, to_date(datum, 'dd.mm.yyyy hh24:mi:ss') datum from test;

ID DATUM
---------- -------------------
1 04.01.2021 00:20:00

SQL>

If you insist on what you asked, then make sure Oracle understands it.

SQL> rollback;

Rollback complete.
SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd/mon/yy hh:mi:ss am';

Session altered.

SQL> insert into test (id, datum) values (1, '4/Jan/21 12:20:00 AM');

1 row created.

SQL> select * From test;

ID DATUM
---------- ---------------------
1 04/jan/21 12:20:00 AM

SQL>

If I were you, I wouldn't do it.

Why date formats are different in APEX and Oracle SQL Developer?

Yes, every application has it's own date format.

And even every application can have more than one session, each with a different session format specified for DATEs and TIMESTAMPs.

SQL Developer has it's application level settings defined in the Preferences, Database, NLS page. This is how DATEs will appear unless you issue an ALTER SESSION SET... in your SQL Worksheet.

Or, if you always want a specific format regardless of this setting, build it into your query.

select to_char(sysdate, 'DAY') today from dual;

Sample Image



Related Topics



Leave a reply



Submit