TO_DATE yy，yyyy给了我不同的结果 [英] TO_DATE yy,yyyy gives me different results

问题描述

如果我运行以下查询

从双中选择to_char(TO_DATE(SYSDATE-7，'DD/MM/YY')，'year')

select to_char( TO_DATE(SYSDATE-7,'DD/MM/YY') ,'year') from dual

结果是二十七

从双中选择to_char(TO_DATE(SYSDATE-7，'DD/MM/YYYY')，'year')

select to_char( TO_DATE(SYSDATE-7,'DD/MM/YYYY') ,'year') from dual

结果是17

为什么第二个查询我得不到27?

why don't I get twenty seventeen for the second query?

推荐答案

因为您正在进行从字符串到日期的不必要的显式转换，所以会根据您的日期从日期到字符串进行隐式转换NLS设置.

Because you are doing an unnecessary explicit conversion from string to date, which is doing an implicit conversion from date to string based on your NLS settings.

您应该做的只是:

select to_char(sysdate - 7, 'year') from dual;

TO_CHAR(SYSDATE-7,'YEAR')                 
------------------------------------------
twenty seventeen

因为sysdate-7已经是日期，所以您不应以任何格式调用to_date().由于to_date()函数采用字符串参数，因此必须先将sysdate-7表达式隐式转换为字符串.所以你真的在做:

Because sysdate-7 is already a date, you should not be calling to_date() around that, with any format. As the to_date() function takes a string argument, your sysdate-7 expression has to be implicitly converted to a string first. So you are really doing:

select to_char(to_date(to_char(sysdate - 7), 'DD/MM/YYYY'), 'year') from dual;

正在使用您的NLS_DATE_FORMAT值进行隐式内部to_char()调用，因此，如果说'DD-MON-RR'，则实际上是在做

which is using your NLS_DATE_FORMAT value for the implicit inner to_char() call, so if that is say 'DD-MON-RR' you're actually doing:

select to_char(to_date(to_char(sysdate - 7, 'DD-MON-RR'), 'DD/MM/YYYY'), 'year') from dual;

要查看其中发生的情况，您需要查看完整的生成日期，为此，我将更改会话的NLS_DATE_FORMAT以显示全年:

To see what's going on in there you need to see what the full generated date is, so for that I'm going to change the NLS_DATE_FORMAT for the session to show the full year:

alter session set nls_date_format = 'SYYYY-MM-DD';

select sysdate - 7 as raw_date,
  to_char(sysdate - 7, 'DD-MON-RR') as implicit_string,
  to_date(to_char(sysdate - 7, 'DD-MON-RR'), 'DD/MM/YY') as implcit_yy,
  to_date(to_char(sysdate - 7, 'DD-MON-RR'), 'DD/MM/YYYY') as implcit_yyyy
from dual;

RAW_DATE    IMPLICIT_STRING    IMPLCIT_YY  IMPLCIT_YYY
----------- ------------------ ----------- -----------
 2017-04-30 30-APR-17           2017-04-30  0017-04-30

请注意最后两个值中的不同年份. 文档中有关于格式模型匹配的部分.您在中间隐式创建的字符串具有两位数的年份.当您使用YY模型将字符串'30-APR-17'转换回日期时，它有帮助"地假定了当前世纪，因此日期最终为2017.但是，当您使用YYYY模型转换相同的字符串时，认为您的意思确实是您传递的价值，因此不假定一个世纪-您通过了17年，因此最终以17年结束；即是0017，而不是2017.(使用RRRR您也会获得预期的答案，但是最好坚持使用YYYY并在所有地方都使用4位数的年份- if 您实际上完全需要使用字符串.)

Notice the different years in the last two values. The documentation has a section on format model matching. The string you are implicitly creating in the middle has a 2-digit year. When you convert the string '30-APR-17' back to a date using a YY model, it 'helpfully' assumes the current century, so the date ends up as 2017. But when you convert the same string using a YYYY model it thinks you really meant the value you passed in and so doesn't assume a century - and you passed it 17, so you end up with the year 17; that is, 0017 and not 2017. (You would also have got the answer you expected using RRRR, but it is much better to stick to YYYY and actually use 4-digit years everywhere - if you actually need to use a string at all.)

本质上:不要依赖NLS设置或日期到字符串的隐式转换，反之亦然.

Essentially: don't rely on NLS settings or implicit conversions of dates to strings or vice versa.

在这种情况下，您不需要进行任何 转换，因此请在此答案的顶部使用更简单的语句.

In this case you don't need any conversions, so use the simpler statement at the top of this answer.

这篇关于TO_DATE yy，yyyy给了我不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！