Oracle 11g与TOAD的不同日期 [英] Different dates Oracle 11g with TOAD

问题描述

我有以下查询:

SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL;  
/*this returns 2/1/2049. */

SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL; 
/*this returns 2/1/1949.*/

为什么第一个返回年份2049而不是1949?

Why does the first one returns the year 2049 instead of 1949?

通过谷歌搜索，我发现我可以通过更改注册表上的key来强制"客户端日期格式成为一种愿望:

By Googling I have found that I can "force" the client date format to be the one desire by changing the keyon the registry:

KEY_OraClient11g_home1
NLS_DATE_FORMAT : YYYY/MM/DD

推荐答案

您正在两个版本中进行多次隐式日期转换.这个:

You're doing multiple implicit date conversions in both versions. This:

SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL; 

等效于:

SELECT to_date(to_char(to_date('01-FEB-1949', <NLS_DATE_FORMAT>),
    <NLS_DATE_FORMAT>, <NLS_DATE_FORMAT>)) FROM DUAL;

而第二个查询将其中一个替换为特定格式.它看起来像您的默认格式-我相信您可以在Toad首选项中进行设置，而无需直接修改注册表.尚不清楚您是否要修改与Toad相关的内容-是否为DD-MON-RR，如将其插入以下查询中所示:

whereas the second query has one of those replaced with a specific format. It looks like your default format - which you can set, I believe, in the Toad preferences without modifying the registry directly; it isn't clear if you're even modifying something related to Toad - is DD-MON-RR, as shown by plugging that into these queries:

SELECT to_date(to_char(to_date('01-FEB-1949','DD-MON-RR'),
        'DD-MON-RR'),'DD-MON-RR') AS date1,
    to_date(to_char(to_date('01-FEB-1949','DD-MON-RR'),
        'dd-MON-yyyy'),'DD-MON-RR') AS date2 FROM DUAL;

DATE1                            DATE2
February, 01 2049 00:00:00+0000  February, 01 1949 00:00:00+0000

( SQL小提琴)

您可以在此SQL提琴中看到，在第一个版本中，日期显示作为年份为49而不是1949的字符串，然后通过RR掩码将其解释为2049，这是预期的行为.

You can see in this SQL Fiddle that in the first version, the date appears as a string with the year as 49 rather than 1949, and that is then interpreted - by the RR mask - as 2049, which is the expected behaviour.

简短版本:永远不要依赖隐式日期转换或NLS日期格式掩码.

Short version: never rely on implicit date conversions or the NLS date format mask.

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