错误：将日期值插入/更新到oracle DB [英] Error: insert / update date values into oracle DB

问题描述

以下是用于更新具有DataTime值的表的代码（简化），其中字段：

Below is the piece of the code (simplified) for updating a table w/ a DataTime value, where the field:

CREATED_DATE

在其DataType中为DATE

is DATE in its DataType

Update  myTable SET CREATED_DATE=5/8/2017 9:17:07 AM WHERE E_ID = 9270002

我在SQL Developer中尝试过这个，但没有成功。我的代码有什么问题？谢谢。



我尝试了什么：



试用SQL Developer还有.Net调试

I tried this in SQL Developer, but not successful. What is wrong in my code? Thanks.

What I have tried:

Tried on SQL Developer and also in .Net debugging

推荐答案

您需要将值放在引号中，否则它会认为您试图划分数字。

You need to put the values in quotes, otherwise it thinks you are trying to divide numbers.

SET CREATED_DATE='5/8/2017 9:17:07 AM'

尝试

Update myTable SET CREATED_DATE='2017-05-08 09:17:07' WHERE E_ID=9270002

请注意，日期不能解释为任何内容2017年5月8日以外。我也认为如果你打算在没有空间的情况下使用AM。我个人认为24小时格式是最好的。



有关详细信息，请参阅了解Oracle SQL日期格式轻松搞定 [ ^ ] < br $>


[更新] OP报告错误。

尝试'2017年5月8日09:17:07 代替。或者尝试

Note that the date cannot be interpreted as anything other than 8th May 2017. I also think if you are going to use "AM" in the time there should be no space. Personally I think 24hr format is best.

For more information see Understanding Oracle SQL Date Format Made Easy[^]

[UPDATE]OP is reporting an error.
Try '2017-MAY-08 09:17:07" instead. Or try

TO_DATE(
    'May 8, 2017, 09:17 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')

参考：去_DATE [ ^ ]

否则看看李nk我提供了更多的想法

Reference:TO_DATE[^]
Otherwise look to the link I provided for more ideas

这篇关于错误：将日期值插入/更新到oracle DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！