将日期插入Oracle [英] Insert Date into Oracle

问题描述

我想在Oracle的日期字段中插入一个日期时间.到目前为止，我已经使用过sysdate，它可以完美工作，但是我需要为其他内容使用相同的时间戳.

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的date字段兼容的datetime变量吗?我尝试的一切都会导致错误.

Does anyone know how I can create a datetime variable that is compatible with oracle's date field? Everything I try causes errors.

我已经尝试过以下方法:

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 & ")")

推荐答案

带参数的查询是一个不错的开始，因为您可以将本机.Net DateTime对象传递给企业库(例如)，并指定它为DbType.DateTime让EL担心它.但是，如果由于未使用EL或类似工具而无法执行此操作，并且坚持使用问题中所张贴的SQL字符串，则您需要了解Oracle在内部如何处理日期，尤其是在格式方面.

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.DateTime and 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.

使用SQL Developer或SQLPlus，执行以下操作:

Using SQL Developer or SQLPlus, execute the following:

Select To_Char(sysdate) from Dual;

显示的格式是您使用To_Date时Oracle实例期望日期使用的格式，以及Oracle将在To_Char中使用的格式.如果您在任何一个呼叫中都偏离了该格式，那么您将遇到问题.但是，无论使用哪种默认格式，都可以通过在调用中指定它来覆盖它:

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:

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;

您可以通过设置NLS_DATE_FORMAT参数来设置希望Oracle隐式使用的默认值:

You can set the default you want Oracle to use implicitly by setting the NLS_DATE_FORMAT parameter:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH:MI:SS'; 

请注意，上一条语句仅更改执行该语句的会话的默认.我们在Oracle系统中使用一种自定义格式，并确保在所有会话中都获得此格式，我们使用登录后触发器:

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:

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; 

可以通过编辑一些Oracle配置文件来更改整个Oracle服务器的默认日期格式，但是由于我们必须部署到无法控制的客户端服务器，因此我们改用此方法.

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.

本文涵盖了只是在示例方面多了一些.

This article covers the same sort of ground I just have with a little more in the way of examples.

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