SQL“不是一个有效的月份”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13002590/
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
SQL "not a valid month"
提问by Matt
I have a table in sql as follows:
我在 sql 中有一个表，如下所示：
CREATE TABLE Reserves(
sid INTEGER,
bid INTEGER,
day DATE,
PRIMARY KEY (sid, bid, day),
FOREIGN KEY (sid) REFERENCES Sailors,
FOREIGN KEY (bid) REFERENCES Boats
);
and I'm trying to insert into it:
我正在尝试插入其中：
INSERT INTO Reserves VALUES(22, 101, '01-01-1998');
But I get the error: ORA-01843: not a valid month
但我收到错误消息：ORA-01843：无效月份
This is an Oracle db. I'm not sure what's wrong with my date format.
这是一个 Oracle 数据库。我不确定我的日期格式有什么问题。
回答by ppeterka
It's not entirely clear which you wanted, so you could try:
目前还不完全清楚你想要哪个，所以你可以尝试：
For month-day-year format:
INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','MM-DD-YYYY'));For day-month-year format:
INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','DD-MM-YYYY'));
对于月-日-年格式：
INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','MM-DD-YYYY'));对于日-月-年格式：
INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','DD-MM-YYYY'));
Also, recommended reading: Oracle functions: TO_DATE
另外，推荐阅读：Oracle 函数：TO_DATE
回答by BellevueBob
You can use the datekeyword to specify an ANSI-standard date string:
您可以使用date关键字来指定 ANSI 标准日期字符串：
INSERT INTO Reserves VALUES(22, 101, date '1998-01-01');
In this case, the format is YYYY-MM-DD, or January 1, 1998.
在这种情况下，格式为 YYYY-MM-DD，或 1998 年 1 月 1 日。
回答by Hamed
As @Jody also mentioned,
You can change the default for your session by executing this code once before INSERT:
正如@Jody 还提到的，
您可以通过在此之前执行一次代码来更改会话的默认值INSERT：
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';
You may alter the format in any order you like.
您可以按您喜欢的任何顺序更改格式。
Source:dba-oracle.com
回答by Shabbir Ali
Also you can try in below query format:
您也可以尝试以下查询格式：
INSERT INTO Reserves VALUES(22, 101, DATE '01-01-1998');
INSERT INTO Reserves VALUES(22, 101, DATE '01-01-1998');
DATEkeyword interprets the following string as a date.
DATE关键字将以下字符串解释为日期。
回答by Jody
Try '1998-01-01'.
试试“1998-01-01”。
I believe the default date format for oracle is yyyy-mm-dd. You can change the default for your session by using alter session set nls_date_format='mm-dd-yyyy'
我相信 oracle 的默认日期格式是 yyyy-mm-dd。您可以使用 alter session set nls_date_format='mm-dd-yyyy' 更改会话的默认值
Keep in mind that most clients let you set this to whatever you like permanently
请记住，大多数客户端都允许您将其永久设置为您喜欢的任何内容
