更新 Oracle SQL 表中的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13497130/
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
Updating a date in Oracle SQL table
提问by Stack Over
I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query:
我正在尝试更新 SQL 表中的日期。我正在使用 Peoplesoft Oracle。当我运行此查询时：
Select ASOFDATE from PASOFDATE;
I get 4/16/2012
我得到 4/16/2012
I tried running this query
我尝试运行此查询
UPDATE PASOFDATE SET ASOFDATE = '11/21/2012';
but it is not working.
但它不起作用。
Does anyone know how I would change the date to the one desired?
有谁知道我如何将日期更改为所需的日期？
回答by Alex Poole
This is based on the assumptionthat you're getting an error about the date format, such as an invalid month value or non-numeric character when numeric expected.
这是基于以下假设：您收到有关日期格式的错误，例如无效的月份值或预期为数字时的非数字字符。
Dates stored in the database do not have formats. When you query the date your client is formatting the date for display, as 4/16/2011. Normally the same date format is used for selecting and updating dates, but in this case they appear to be different - so your client is apparently doing something more complicated that SQL*Plus, for example.
存储在数据库中的日期没有格式。当您查询日期时，您的客户端正在格式化显示日期，如4/16/2011. 通常，相同的日期格式用于选择和更新日期，但在这种情况下，它们似乎不同 - 例如，您的客户显然正在做一些比 SQL*Plus 更复杂的事情。
When you try to update it it's using a default date format model. Because of how it's displayed you're assuming that is MM/DD/YYYY, but it seems not to be. You could find out what it is, but it's better not to rely on the default or any implicit format models at all.
当您尝试更新它时，它使用的是默认日期格式模型。由于它的显示方式，您假设是MM/DD/YYYY，但似乎不是。您可以找出它是什么，但最好不要依赖默认或任何隐式格式模型。
Whether that is the problem or not, you should always specify the date model:
无论这是否是问题，您都应该始终指定日期模型：
UPDATE PASOFDATE SET ASOFDATE = TO_DATE('11/21/2012', 'MM/DD/YYYY');
Since you aren't specifying a time component - all Oracle DATEcolumns include a time, even if it's midnight - you could also use a date literal:
由于您没有指定时间组件 - 所有 OracleDATE列都包含时间，即使是午夜 - 您也可以使用日期文字：
UPDATE PASOFDATE SET ASOFDATE = DATE '2012-11-21';
You should maybe check that the current value doesn't include a time, though the column name suggests it doesn't.
您可能应该检查当前值是否不包含时间，尽管列名表明它不包含。
回答by irupmarakymaws
Here is how you set the date and time:
以下是设置日期和时间的方法：
update user set expiry_date=TO_DATE('31/DEC/2017 12:59:59', 'dd/mm/yyyy hh24:mi:ss') where id=123;
回答by navi
If this SQL is being used in any peoplesoft specific code (Application Engine, SQLEXEC, SQLfetch, etc..) you could use %Datein metaSQL. Peopletools automatically converts the date to a format which would be accepted by the database platform the application is running on.
如果在任何 peoplesoft 特定代码（应用程序引擎、SQLEXEC、SQLfetch 等）中使用此 SQL，您可以使用 %Datein metaSQL。Peopletools 自动将日期转换为运行应用程序的数据库平台可接受的格式。
In case this SQL is being used to perform a backend update from a query analyzer (like SQLDeveloper, SQLTools), the date format that is being used is wrong. Oracle expects the date format to be DD-MMM-YYYY, where MMM could be JAN, FEB, MAR, etc..
如果此 SQL 用于从查询分析器（如 SQLDeveloper、SQLTools）执行后端更新，则所使用的日期格式是错误的。Oracle 期望日期格式为 DD-MMM-YYYY，其中 MMM 可以是 JAN、FEB、MAR 等。
回答by Nick
Just to add to Alex Poole's answer, here is how you do the date and time:
只是为了补充 Alex Poole 的答案，以下是您如何处理日期和时间：
TO_DATE('31/DEC/2017 12:59:59', 'dd/mm/yyyy hh24:mi:ss')
