Oracle 插入失败:不是有效月份

2022-01-09 00:00:00 datetime sql insert oracle

我正在尝试从包含日期列的 .xlsx 电子表格中导入数据.在这些列中，日期以 DD-MON-YY 格式显示(例如:20-AUG-12).

I'm trying to import data from an .xlsx spreadsheet which contains date columns. In those columns, dates are displayed in the DD-MON-YY format (for example : 20-AUG-12).

当我运行导入向导时，一切正常，直到我必须精确列/字段映射.我有一个免责声明，说所选格式与表字段定义不匹配(我的字段是日期字段).插入脚本示例:

When I'm running the import wizard, everything goes fine until I must precise the columns/fields mapping. I've got a disclaimer saying that the chosen format is not matching the table field definition (my field is a date field). An example of the insert script : 

INSERT INTO simulation 
            (simulation_id, 
             simulation_name, 
             sim_start_date, 
             sim_end_date, 
             status, 
             last_run_date, 
             moddat, 
             modusr, 
             notification_email) 
VALUES      (251.0, 
             'Proposal Test', 
             To_date('01-DEC-11', 'DD-MON-YY'), 
             To_date('31-DEC-11', 'DD-MON-YY'), 
             'C', 
             To_date('09-AUG-12', 'DD-MON-YY'), 
             To_date('09-AUG-12', 'DD-MON-YY'), 
             'Brent Weintz', 
             'bweintz@carlsonwagonlit.com');

当我尝试运行此查询时，出现以下错误:

When I'm trying to run this query, I got the following error :

ORA-01843: 月份无效01843. 00000 - 不是一个有效的月份"

ORA-01843: not a valid month 01843. 00000 - "not a valid month"

如您所见，格式匹配，我不知道问题出在哪里...有什么想法吗?

As you can see, the formats are matching and I can't figure out where the problem is... Any idea guys?

我的日期语言参数设置为法语".可以改成AMERICAN"吗?

EDIT : My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?

推荐答案

虽然你使用 to_date 和正确的格式模型显式转换成日期，还是得到 ORA-01843 似乎问题出在 NLS_DATE_LANGUAGE 上.在此处了解更多信息.

Although you are using to_date and correct format model to explicitly convert into date, still getting ORA-01843 it seems like the issue is with the NLS_DATE_LANGUAGE. Read more about it here.

SQL> DROP TABLE t PURGE;

Table dropped.

SQL> CREATE TABLE t(A DATE);

Table created.

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH';

Session altered.

SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';

PARAMETER            VALUE
-------------------- ----------
NLS_DATE_LANGUAGE    FRENCH

SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'));
INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'))
                             *
ERROR at line 1:
ORA-01843: not a valid month

所以，我遇到了不正确的 NLS_DATE_LANGUAGE 错误.让我们将其设置为 AMERICAN.

So, I encounter the error with improper NLS_DATE_LANGUAGE. Let's set it to AMERICAN. 

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

Session altered.

SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';

PARAMETER            VALUE
-------------------- ----------
NLS_DATE_LANGUAGE    AMERICAN

SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'));

1 row created.

SQL>

更新至 OP 已编辑的问题 -

UPDATE To OP's edited question -

我的日期语言参数设置为法语".可以改成AMERICAN"吗?

My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?

来自 文档，以下是NLS 参数的设置方法.注意优先顺序.

相关文章