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
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>