Why do I set the time format, but still report an error

Question

I try to insert data after I set the format, but I get a time format error

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

INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

ora-01843 I'm wondering how can I handle different time formats, I've come across several different time formats in others code

after alter session set nls_date_language = 'english'; now I can insert data normally , there is a new question no matter how i set ALTER SESSION SET NLS_DATE_FORMAT='mon-dd-YYYY'; or ALTER SESSION SET NLS_DATE_FORMAT='yyyy.dd.mon'; Nothing changes for my inserted data enter image description here

Answer 1

Not every language abbreviates the first month of the year as "JAN". It appears you want

ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH'

---

Either that, or consider converting textual month names to numbers before handing them to the database.

Answer 2

There are various ways to handle dates; all you have to do, is to make sure that you're in control over it. Don't rely on implicit datatype conversion and insert strings instead of dates. And - if you insert dates, do it in correct format. Here are several examples:

SQL> create table test (datum date);

Table created.

Using date literal, which always consists of date keyword and date value enclosed into single quotes in format yyyy-mm-dd:

SQL> insert into test values (date '2023-03-27');

1 row created.

Using to_date function with appropriate format model:

SQL> insert into test values (to_date('27.03.2023', 'dd.mm.yyyy'));

1 row created.

to_date again, but this time month is spelled - in that case, specify which language you use:

SQL> insert into test values (to_date('mar-27-2023', 'mon-dd-yyyy', 'nls_date_language = english'));

1 row created.

Alter session so that string - entered in that format and language - is correctly recognized:

SQL> alter session set nls_date_format = 'yyyy.dd.mon';

Session altered.

SQL> alter session set nls_date_language = 'croatian';

Session altered.

SQL> insert into test values ('2023.27.ožu');

1 row created.

SQL>

---

According to the last NLS_DATE_FORMAT, this is table contents:

SQL> select * from test;

DATUM
-----------
2023.27.ožu
2023.27.ožu
2023.27.ožu
2023.27.ožu

If I alter session and set it differently:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select * from test;

DATUM
----------
27.03.2023
27.03.2023
27.03.2023
27.03.2023

SQL>

Or, I can use `to_char` function with any valid format model:

SQL> select to_char(datum, 'yyyy-mon-dd', 'nls_date_language = english') from test;

TO_CHAR(DAT
-----------
2023-mar-27
2023-mar-27
2023-mar-27
2023-mar-27

SQL>

---

Yet another example (returning values from sysdate function and Scott's emp table):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
27.03.2023 09:45:14       --> both date and time are displayed

SQL> select hiredate from emp where rownum = 1;

HIREDATE
-------------------
17.12.1980 00:00:00      --> date is here, but time is set to midnight because that's what column contains

SQL>

Answer 3

In Oracle, a DATE is a binary data type that consists of 7 bytes representing century, year-of-century, month, day, hours, minutes and seconds. It ALWAYS has those 7 components and it is NEVER stored in any particular human-readable format.

The NLS_DATE_FORMAT session parameter is the format model Oracle uses for IMPLICIT string-to-date and date-to-string conversions.

If you are performing an string-to-date or date-to-string conversion and are EXPLICIT and specify the format model then your format model will be used in preference to the NLS_DATE_FORMAT session parameter.

Implicit Date Conversions

If you do:

CREATE TABLE table_name (dt DATE);

Then:

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

INSERT INTO table_name (dt) VALUES ('27-MAR-2023'); -- A string
INSERT INTO table_name (dt) VALUES (TO_DATE('27-MAR-2023')); -- No format model

Then:

• In the first insert, Oracle will convert the string to a date and implicitly calls TO_DATE and will implicitly use the NLS_DATE_FORMAT session parameter as the format model.
• In the second insert, TO_DATE is explicitly called but no format model is specified so Oracle will implicitly use the NLS_DATE_FORMAT as the format model.

Both of these INSERTs are effectively the same as:

INSERT INTO table_name (dt) VALUES (
  TO_DATE(
    '27-MAR-2023',
    (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
  )
);

Explicit Date Conversions

When you are explicit about the format model:

INSERT INTO table_name (dt) VALUES (TO_DATE('27-MAR-2023', 'DD-MON-YYYY'));

Then Oracle does not need to use the NLS_DATE_FORMAT.

Handling different date formats

I'm wondering how can I handle different time formats, I've come across several different time formats in others code

Using implicit conversions is considered bad practice. Always explicitly specify the format model and, if you are using language-specific formats then specify the language in the query:

INSERT INTO table_name (dt)
VALUES (TO_DATE('27-MAR-2023', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English'));

or, better, use a DATE literal:

INSERT INTO table_name (dt) VALUES (DATE '2023-03-27');

Which does not need to be converted from a string to a date and requires no language-specific conversions.

Displaying data

No matter how I set ALTER SESSION SET NLS_DATE_FORMAT='mon-dd-YYYY'; or ALTER SESSION SET NLS_DATE_FORMAT='yyyy.dd.mon'; Nothing changes for my inserted data.

A DATE is a binary data type and is NEVER stored in any particular human-readable format.

The NLS_DATE_FORMAT is only used for implicit string-to-date and date-to-string conversions.

If you are displaying dates then the database will provide the client application with the binary value of the date and it is then up to the client application to display that binary value in a meaningful way to the user.

If you are using SQL*Plus or SQL Developer (both Oracle products) then they will both use the NLS_DATE_FORMAT as the default format for displaying dates. If you are using a different client application (which, for your image, it looks like you are, maybe TOAD or PL/SQL Developer) then how it displays dates is something that that client application controls (and not the database) and if you want to change how it formats dates to display them then you will need to change the settings/preferences in that client application.

If you want to use a specific format and be independent of the client application then you can use TO_CHAR in a SELECT statement to convert the binary DATE to a formatted string:

SELECT dt,                        -- Formatted by the client application
       TO_CHAR(dt),               -- Converted to a string by the database
                                  -- Implicitly uses NLS_DATE_FORMAT as the format
                                  -- model.
       TO_CHAR(dt, 'DD-MON-YYYY') -- Converted to a string by the database
                                  -- Uses the explicitly provided format model.
FROM   table_name;