First time using Oracle SQL (I'm used to MySQL). I'm finding conflicting info on what the default date format is. After several attempts having to use TO_DATE with my INSERT INTO my_table statements, I finally found the database I'm using expects DD-MON-YY (i.e. 25-JAN-18). Yet on various pages here in stackoverflow and elsewhere, I see some that say default is YYYYMMDD or DD/MM/YYYY or YYYY-MM-DD. Why so many conflicting pieces of information?
Finally, you can change the default DATE format of Oracle from "DD-MON-YY" to something you like by issuing the following command in sqlplus: alter session set NLS_DATE_FORMAT='<my_format>'; The change is only valid for the current sqlplus session.
Oracle date format The standard date format for input and output is DD-MON-YY e.g., 01-JAN-17 which is controlled by the value of the NLS_DATE_FORMAT parameter. The following statement returns the current date with the standard date format by using the SYSDATE function.
The default datetime formats are specified either explicitly with the NLS session parameters NLS_DATE_FORMAT , NLS_TIMESTAMP_FORMAT , and NLS_TIMESTAMP_TZ_FORMAT , or implicitly with the NLS session parameter NLS_TERRITORY . You can change the default datetime formats for your session with the ALTER SESSION statement.
By default, Oracle SQL developer displays date values as 15-NOV-11 .
A DATE
has no format - it is stored internally as 7-bytes representing year (2 bytes) and month, day, hour, minute and second (1 byte each).
'25-JAN-18'
is not a date - it is a text literal.
When you do:
INSERT INTO table_name ( date_column ) VALUES ( '25-JAN-18' );
Oracle will try to be helpful and perform an implicit cast from a string to a date using the NLS_DATE_FORMAT
parameter for the user's session as the format model. So, your statement will be implicitly converted to:
INSERT INTO table_name ( date_column ) VALUES (
TO_DATE(
'25-JAN-18',
( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
)
);
Any user can set their NLS parameters in their own session (so you should never rely on implicit conversion as each user can have a different settings for their own session and can change the values mid-session). Instead you should:
Use a Date literal:
DATE '2018-01-25'
Use a Timestamp literal
TIMESTAMP '2018-01-25 01:23:45'
Use TO_DATE( date_string, format_string [, nls_values] )
and explicitly use a format model:
TO_DATE( '25-JUN-18', 'DD-MON-RR' )
If you do want to change the NLS_DATE_FORMAT
in your session then you can use:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
What the default date format is?
Since a DATE
does not have a format, this question does not make sense. Instead if we ask:
What is the default
NLS_DATE_FORMAT
session parameter that Oracle uses to convert between strings and dates?
It depends on the NLS_TERRITORY
session parameter (so it depends where you are in the world):
SET SERVEROUTPUT ON;
VARIABLE cur REFCURSOR;
DECLARE
territories SYS.ODCIVARCHAR2LIST;
formats SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
select value
BULK COLLECT INTO territories
from v$nls_valid_values
where parameter = 'TERRITORY'
order by value;
formats.EXTEND( territories.COUNT );
FOR i IN 1 .. territories.COUNT LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY='''||territories(i)||'''';
SELECT value
INTO formats(i)
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';
END LOOP;
OPEN :cur FOR
SELECT CAST( f.format AS VARCHAR2(12) ) AS format,
LISTAGG( t.territory, ', ' ) WITHIN GROUP ( ORDER BY t.territory ) AS territories
FROM ( SELECT ROWNUM AS rn, COLUMN_VALUE AS territory FROM TABLE( territories ) ) t
INNER JOIN
( SELECT ROWNUM AS rn, COLUMN_VALUE AS format FROM TABLE( formats ) ) f
ON ( f.rn = t.rn )
GROUP BY f.format;
END;
/
PRINT :cur;
Outputs the date format and the list of territories corresponding to that format:
FORMAT TERRITORIES
------------ ------------------------------------------------------------------
DD MON RRRR THAILAND
DD-MM-RR ALGERIA, BAHRAIN, INDIA, MOROCCO, THE NETHERLANDS, TUNISIA
DD-MM-RRRR BANGLADESH, INDONESIA, ROMANIA, VIETNAM
DD-MON-RR AMERICA, CHINA, HONG KONG, IRELAND, ITALY, PAKISTAN, TAIWAN,
UNITED KINGDOM
DD-MON-RRRR ISRAEL
DD.MM.RR AUSTRIA, BELARUS, CIS, CROATIA, CZECH REPUBLIC, CZECHOSLOVAKIA,
GERMANY, RUSSIA, SLOVAKIA, SLOVENIA, SWITZERLAND
DD.MM.RRRR ALBANIA, AZERBAIJAN, ESTONIA, FINLAND, FYR MACEDONIA, ICELAND,
KAZAKHSTAN, MACEDONIA, NORWAY, SERBIA AND MONTENEGRO, UKRAINE,
YUGOSLAVIA
DD.MM.RRRR. MONTENEGRO, SERBIA
DD.fmMM.RRRR ARMENIA
DD/MM/RR AFGHANISTAN, BELGIUM, BRAZIL, CAMEROON, CATALONIA, CHILE, COLOMBIA,
CONGO BRAZZAVILLE, CONGO KINSHASA, COSTA RICA, CYPRUS, DJIBOUTI,
EGYPT, EL SALVADOR, FRANCE, GABON, GREECE, GUATEMALA, HONDURAS,
IRAQ, IVORY COAST, JORDAN, KUWAIT, LEBANON, LIBYA, LUXEMBOURG,
MAURITANIA, MEXICO, NEW ZEALAND, NICARAGUA, OMAN, PANAMA, PERU,
PUERTO RICO, QATAR, SAUDI ARABIA, SINGAPORE, SOMALIA, SPAIN, SUDAN,
SYRIA, UNITED ARAB EMIRATES, URUGUAY, VENEZUELA, YEMEN
DD/MM/RRRR ARGENTINA, BAHAMAS, BERMUDA, ECUADOR, MALAYSIA, SENEGAL, TURKEY,
UGANDA, ZAMBIA
DD/MON/RR AUSTRALIA, SOUTH AFRICA, UZBEKISTAN
DD/fmMM/RRRR LAOS, NIGERIA
MM/DD/RRRR PHILIPPINES
RR-MM-DD CANADA, DENMARK, JAPAN
RR-MON-DD HUNGARY
RR.MM.DD PORTUGAL
RR/MM/DD KOREA, POLAND
RRRR-MM-DD BULGARIA, SWEDEN
RRRR-fmMM-DD CAMBODIA
RRRR.MM.DD LATVIA, LITHUANIA
RRRR/fmMM/fm IRAN, SRI LANKA
fmDD-MM-RR BOLIVIA
fmDD/MM/RR PARAGUAY
fmDD/MM/RRRR BELIZE, ETHIOPIA, MALTA, NEPAL
fmDD/fmMM/RR MALDIVES
fmMM.DD.RRRR BOSNIA AND HERZEGOVINA
fmMM/DD/RRRR KENYA, TANZANIA
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With