Oracle和.NET的TimeZone时间/日期格式 [英] TimeZone Time/Date Formats with Oracle and .NET

问题描述

我是Oracle的新手，希望有人能为我澄清这种情况.

I am new to Oracle and hope someone can clarify this scenario for me.

我编写了一个非常大的查询，该查询返回了一组日期(Oracle 10g).

I have written a very large query that returns a set of dates with it (Oracle 10g).

在SQL Developer中，这些日期以DD-MON-YY格式显示(例如:"12-MAY-12").在SQL Developer中，该字段的数据类型显示为日期类型，并且选中了单选"按钮.当我在SQL Developer中运行此查询时，得到的日期看起来像这样:

In SQL Developer, these dates appear in DD-MON-YY format (ex: '12-MAY-12'). The field's data type in SQL Developer shows it is a Date type, and the Simple radio button is checked. When I run this query in SQL Developer, I get a date that looks like this:

12年5月11日

我在属于C#Web服务的程序集中使用此查询.托管我们的QE服务器的离岸团队中的某人注意到，该字段的值正在转换为IST偏移量(已使用GetXml for DataSet将其转换为XML).问题是，直到我在调试器中检查了输出，我什至都不知道偏移量在那里，因为在SQL Developer中看不到偏移量.它像这样过来:

I use this query in an assembly that is part of a C# web service. Someone on our offshore team where our QE servers are hosted noticed that the field's value is being converted to an IST offset (it's converted to XML using GetXml for the DataSet). The thing is, I didn't even know the offset was there until I inspected the output in a debugger since I can't see it in SQL Developer. It's coming over like this:

2012-05-11T06:09:15 + 05:30

2012-05-11T06:09:15+05:30

没有使用内部日期转换功能，仅使用提取日期的字段名称.

There's no intrinsic date conversion function used, just the name of the field that pulls out the date.

如何修改查询，以便在运行查询时不考虑偏移量(我们确实需要保留Date的时间部分)?我知道这个问题听起来很复杂，但最终结果是将数据写入海外的另一个数据库包含错误的偏移量，因此将其转换为相差1天的日期.

How can I modify my query so that the offset coming over (we do need to preserve the time portion of the Date) is not taken into consideration when the query is run? I know the question sounds convoluted, but the end result is data being written to another database overseas contains the wrong offset, so it's being converted to dates that are off by 1 day.

基本上我想要时间，但不需要偏移量.

Basically I want the time but not the offset.

推荐答案

Oracle DATE具有日期和时间部分，但没有时区. TIMESTAMP WITH [LOCAL] TIME ZONE将具有时区，并且还将具有亚秒级的精度.

An Oracle DATE has a day and a time component but no time zone. A TIMESTAMP WITH [LOCAL] TIME ZONE will have a time zone and will also have sub-second precision.

您可以通过几种不同的方式更改将DATE数据转换为字符串以进行显示的方式.首先，它使用显式的TO_CHAR

You can change how the DATE data is converted into a string for display in a few different ways. First, it to use an explicit TO_CHAR

SELECT to_char( <<your_date_column>>, 'YYYY-MM-DD HH24:MI:SS' )
  FROM your_table

第二，您可以更改会话的NLS_DATE_FORMAT，以便会话始终以特定格式显示数据

Second, you can change the NLS_DATE_FORMAT of your session so that the session always displays data in a particular format

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT <<your_date_column>>
  FROM your_table

第三，在SQL Developer中，您可以更改其用于显示DATE数据的字符串格式.如果转到Tools | Preferences | Database | NLS，将出现一个Date Format字段.如果将其从DD-MON-RR更改为YYYY-MM-DD HH24:MI:SS，您将获得默认显示的日期和时间.

Third, in SQL Developer, you can change the string format it uses to display DATE data. If you go to Tools | Preferences | Database | NLS, there will be a Date Format field. If you change that from DD-MON-RR to YYYY-MM-DD HH24:MI:SS, you'll get the day and the time displayed by default.

这篇关于Oracle和.NET的TimeZone时间/日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！