oracle ORA-01843: 不是有效月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13107900/
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
ORA-01843: not a valid month
提问by Nina
I am having an issue with my sql query.
我的 sql 查询有问题。
create table rental
(
rental_id NUMBER(5) NOT NULL,
rental_date timestamp NOT NULL,
inventory_id NUMBER(5) NOT NULL,
customer_id NUMBER(5) NOT NULL,
return_date timestamp NOT NULL,
staff_id NUMBER(5) NOT NULL,
constraint rental_primary PRIMARY KEY (rental_id),
constraint rental_foreign FOREIGN KEY (inventory_id) REFERENCES
inventory(inventory_id),
constraint rental_foreign2 FOREIGN KEY (customer_id) REFERENCES
customer(customer_id),
constraint rental_foreign3 FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
constraint rental_unique_rental_date unique (rental_date),
constraint rental_unique_inventory_id unique (inventory_id),
constraint rental_unique_customer_id unique (customer_id),
constraint rental_rental_date check(to_char(rental_date,'YYYY/MM/DD HH:MI:SS AM')
between '2005/01/01 00:00:01 AM' AND '12/31/2015 11:59:59 PM'),
constraint rental_return_date check(to_char(return_date,'YYYY/MM/DD HH:MI:SS AM')
between '2005/01/01 00:00:01 AM' AND '12/31/2015 11:59:59 PM')
);
when I am trying to insert data, It is throwing this error "ORA-01843: not a valid month". Can anyone please suggest me what type of datatype can I use for rental_date and return date to get through this situation? Here is my sample data which I am trying to upload
当我尝试插入数据时，它抛出此错误“ORA-01843：无效月份”。任何人都可以建议我使用什么类型的数据类型来处理rental_date和return date来解决这种情况？这是我尝试上传的示例数据
rental_date return_date
5/24/2011 10:53:30 PM 5/26/2011 10:04:30 PM
Note : I can see this type of format in my excel sheet where I have my data but when I get error
注意：我可以在我有数据的 Excel 表中看到这种类型的格式，但是当我收到错误时
"ORA-01843: not a valid month
Row 145: 144, 2011-05-25 23:49:56 ,1689,357, 2011-06-01 21:41:56 ,2"
I am observing different format of time here.
我在这里观察不同的时间格式。
Can anyone suggest me a solution?
谁能建议我一个解决方案？
Thanks in advance.
提前致谢。
回答by APC
In your check constraints you specify TO_CHAR() with a date format mask of 'YYYY/MM/DD HH:MI:SS AM'. But the values you specify for the range are in two different formats, e.g.
在检查约束中，您使用日期格式掩码指定 TO_CHAR() 'YYYY/MM/DD HH:MI:SS AM'。但是您为范围指定的值有两种不同的格式，例如
'2005/01/01 00:00:01 AM' AND '12/31/2015 11:59:59 PM'
I think it would be a good idea to work with dates instead, as strings won't compare the way you think they do.
我认为改用日期是个好主意，因为字符串不会像您认为的那样比较。
constraint rental_rental_date check(rental_date)
between to_date('2005/01/01 00:00:01 AM', 'YYYY/MM/DD HH:MI:SS AM') and
to_date('2015/12/31 11:59:59 PM', 'YYYY/MM/DD HH:MI:SS AM')
However this may not be the root of your problem. It may be an conversion in your load process, which is why we need to know how you're loading the data.
但是，这可能不是您问题的根源。这可能是您加载过程中的一个转换，这就是为什么我们需要知道您是如何加载数据的。
