如何在 Oracle 10g xe 中插入日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21167482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert date in Oracle 10g xe?
提问by Vivek
I did the following:
我做了以下事情：
CREATE TABLE BOOK( BOOK_ID VARCHAR(4) PRIMARY KEY, ISBN_10 VARCHAR(10), TITLE VARCHAR(50), CATEGORY VARCHAR(25), PRICE DECIMAL(6,2), BINDING VARCHAR(2), PUB_DATE DATE, AUTHOR_ID SMALLINT, PUBLISHER_ID SMALLINT );
CREATE TABLE BOOK( BOOK_ID VARCHAR(4) PRIMARY KEY, ISBN_10 VARCHAR(10), TITLE VARCHAR(50), CATEGORY VARCHAR(25), PRICE DECIMAL(6,2), BINDING VARCHAR(2), PUB_DATE DATE, AUTHOR_ID SMALLINT, PUBLISHER_ID SMALLINT );
2.
2.
ALTER SESSION SET nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
3.
3.
INSERT INTO BOOK
VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S',**'26-01-1991'**,13,103);
It gave an error: ORA-01843: not a valid month
它给出了一个错误： ORA-01843：无效月份
However if do the followingthere is no problem:
但是，如果执行以下操作，则没有问题：
Query:
询问：
INSERT INTO
BOOK
VALUES('4','123459','INTRODUCTION TO Small Talk','IT',157.00,'S','**26-JAN-1991**',13,103);
Can anyone explain why?
谁能解释为什么？
回答by álvaro González
You want to read the Datetime Literalssection of the manual. Alternatives are:
您想阅读手册的日期时间文字部分。替代方案是：
- Use a date literal:
DATE '1991-01-26' - Convert from string:
TO_DATE('26-01-1991', 'DD-MM-YYYY')
- 使用日期文字：
DATE '1991-01-26' - 从字符串转换：
TO_DATE('26-01-1991', 'DD-MM-YYYY')
If you set NLS_DATE_FORMATyou can omit TO_DATE()'s second argument but not skip the function entirely.
如果设置NLS_DATE_FORMAT，则可以省略TO_DATE()的第二个参数，但不能完全跳过该函数。
See also: Datetime Format Models
另请参阅：日期时间格式模型
回答by OldProgrammer
It worked for me. That implies that your alter statement did not work for some reason. However, it is not good practice to assume a specific date format on a system when using literals. Instead, cast the literal with a format mask on the insert, such as:
它对我有用。这意味着您的 alter 语句由于某种原因不起作用。但是，在使用文字时在系统上假设特定的日期格式并不是一个好习惯。相反，在插入中使用格式掩码转换文字，例如：
INSERT INTO BOOK
VALUES('4','123459','INTRODUCTION TO Small Talk','IT',157.00,'S',
to_date('26-JAN-1991','DD-MON-YYYY'), 13,103);
(or whatever format you require as a literal input value)
（或您需要的任何格式作为文字输入值）
回答by San
This because in your database, nls_date_time is set to 'DD-MON-RRRR' format, you can check this using
select * from V$NLS_PARAMETERS
这是因为在您的数据库中，nls_date_time 设置为“DD-MON-RRRR”格式，您可以使用
select * from V$NLS_PARAMETERS
In this query
在这个查询中
INSERT INTO BOOK VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S','26-01-1991',13,103);
INSERT INTO BOOK VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S','26-01-1991',13,103);
date format is 'dd-mm-rrrr', change the statement as
日期格式为'dd-mm-rrrr'，将语句改为
INSERT INTO BOOK VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S',to_date('26-01-1991', 'dd-mm-rrrr'),13,103);
INSERT INTO BOOK VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S',to_date('26-01-1991', 'dd-mm-rrrr'),13,103);
and it will also run since you have now provided the date format.
它也会运行，因为您现在提供了日期格式。
回答by Wernfried Domscheit
When you set
当你设置
ALTER SESSION SET nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
then your insert must match this format, i.e.
那么你的插入必须匹配这种格式，即
INSERT INTO BOOK VALUES('4','123459','INTRODUCTION TO SmallTalk','IT',157.00,'S',
'26-01-1991 00:00:00'
,13,103);
However, the more secure way is to use date literals or TO_DATE function.
但是，更安全的方法是使用日期文字或 TO_DATE 函数。
回答by user3480885
You just need to use
你只需要使用
INSERT INTO TABLE_NAME VALUES('07-JAN-96');
Oracle always takes "DD-MMM-YY"format. You should write your month as the first 3 characters of month name.
Oracle 总是采用"DD-MMM-YY"格式。您应该将月份写为月份名称的前 3 个字符。
