vb.net 将日期插入 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15094732/
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
Insert Date into Oracle
提问by Chris
I want to insert a datetime into a date field in oracle. I have up until now used sysdate, which works perfectly, however I need to use the same timestamp for something else.
我想在 oracle 的日期字段中插入日期时间。到目前为止，我一直使用 sysdate，它运行良好，但是我需要对其他内容使用相同的时间戳。
Does anyone know how I can create a datetime variable that is compatible with oracle's date field? Everything I try causes errors.
有谁知道我如何创建一个与 oracle 的日期字段兼容的日期时间变量？我尝试的一切都会导致错误。
I have tried variations along the lines of this:
我已经尝试了以下方面的变化：
Dim timestamp As DateTime = CDate(Now.ToString("yyyy/MM/dd HH:mm:ss"))
.......more code....
insertBuilder.Append("date = to_date(" & timestamp & ")")
回答by Steven Doggart
First, you seem to be confused about the difference between a DateTimeobject and the formatted Stringrepresentation of that DateTimeobject. Now, or even better, DateTime.Nowis already a DateTimeobject, so it makes no sense to format it as a string and then parse the string to get it back into a DateTimevalue again. So, you can simply do this to accomplish the same thing:
首先，您似乎对DateTime对象与该对象的格式化String表示之间的区别感到困惑DateTime。 Now，或者甚至更好，DateTime.Now已经是一个DateTime对象，因此将其格式化为字符串然后解析该字符串以DateTime再次将其恢复为值是没有意义的。所以，你可以简单地这样做来完成同样的事情：
Dim timestamp As Date = Date.Now
Note that in VB.NET, Dateis a keyword that is short for DateTime, just as Integeris "short" for Int32.
请注意，在VB.NET，Date是很短的关键字DateTime，就像Integer是“短” Int32。
Second, you should not be appending DateTimevalues directly into the SQL command string. You should be using a parameterized query. When you append the DateTimevalue to the SQL string, you must make sure it is formatted properly (by calling timestamp.ToString(...)). Unfortunately, however, which format is proper will depend entirely on the culture settings of the server. So, it is far better to use a DB parameter, set the parameter value equal to the actual DateTimeobject, and then let the DB provider do the conversion for you.
其次，您不应将DateTime值直接附加到 SQL 命令字符串中。您应该使用参数化查询。将DateTime值附加到 SQL 字符串时，必须确保其格式正确（通过调用timestamp.ToString(...)）。然而不幸的是，哪种格式正确将完全取决于服务器的文化设置。因此，最好使用 DB 参数，将参数值设置为与实际DateTime对象相等，然后让 DB 提供程序为您进行转换。
回答by Steve Pettifer
A parameterised query is a good start because you can pass a native .Net DateTime object into Enterprise Library (for example) and specify that it is of DbType.DateTimeand let EL worry about it. However if you cannot do that because you aren't using EL or something similar and are stuck with using SQL strings as posted in your question then you need to be aware of how Oracle treats dates internally, particularly with regards to format.
参数化查询是一个好的开始，因为您可以将本机 .Net DateTime 对象传递到企业库（例如）并指定它是 ofDbType.DateTime并让 EL 担心它。但是，如果您不能这样做，因为您没有使用 EL 或类似的东西，并且坚持使用问题中发布的 SQL 字符串，那么您需要了解 Oracle 如何在内部处理日期，特别是在格式方面。
Using SQL Developer or SQLPlus, execute the following:
使用 SQL Developer 或 SQLPlus，执行以下操作：
Select To_Char(sysdate) from Dual;
The format which is displayed is the format which your Oracle instance expects dates to be in if you use To_Date and which Oracle will use in To_Char. If you deviate from that format in either call then you will get problems. However, regardless of the default format being used you can override it by specifying it in the call:
显示的格式是您的 Oracle 实例在您使用 To_Date 时期望日期采用的格式，以及 Oracle 将在 To_Char 中使用的格式。如果您在任一调用中偏离该格式，那么您将遇到问题。但是，无论使用哪种默认格式，您都可以通过在调用中指定它来覆盖它：
Select To_Char(sysdate, 'YYYY/MM/DD HH:MI:SS') from Dual;
Select To_Date('2013/02/26 05:03:27', 'YYYY/MM/DD HH:MI:SS') from Dual;
You can set the default you want Oracle to use implicitly by setting the NLS_DATE_FORMAT parameter:
您可以通过设置 NLS_DATE_FORMAT 参数来设置您希望 Oracle 隐式使用的默认值：
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH:MI:SS';
Note that the previous statement only alters the default for the session in which it is executed. We use a custom format in our Oracle systems and to ensure that we get it on all sessions we use an after logon trigger:
请注意，前面的语句仅更改执行它的会话的默认值。我们在我们的 Oracle 系统中使用自定义格式，并确保我们在使用登录后触发器的所有会话中获取它：
create or replace TRIGGER NLS_Parameters_OnLogon AFTER LOGON ON SCHEMA BEGIN
-- This makes the database case insensitive for sorting and searching and sets the default date format for TO_CHAR and TO_DATE
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY/MM/DD HH:MI:SS''';
End;
It is possible to change the default date format for the whole Oracle server by editing some of the Oracle config files but since we have to deploy to client servers over which we have no control we use this method instead.
可以通过编辑一些 Oracle 配置文件来更改整个 Oracle 服务器的默认日期格式，但由于我们必须部署到我们无法控制的客户端服务器，因此我们改用此方法。
This articlecovers the same sort of ground I just have with a little more in the way of examples.
这篇文章涵盖了我所拥有的相同类型的基础，并以更多示例的方式进行了介绍。
