无法理解值如何隐式转换为日期格式

2021-12-30 00:00:00 sql date oracle11g oracle

当我检查我的 NLS_DATE_FORMAT 是 DD-MM-RR.

As I checked my NLS_DATE_FORMAT is DD-MM-RR.

考虑带有日期列的 mytable，当我执行以下语句时，它会抛出错误ORA-01840:输入值对于日期格式而言不够长"，这是可以理解的.

Considering mytable with a date column,when I execute the following statement, it throws error "ORA-01840: input value not long enough for date format"which is reasonably understandable. 

insert into mytable values('10'); //Error: reason understandable

那么为什么下面的语句在输入大小明显大于默认DATE Format的大小的情况下有效.

Then why is following statement valid where clearly the input size is greater than size of default DATE Format. 

insert into mytable values('10AUGUST2016'); //reason not understandable

这里我也没有使用任何 TO_DATE 函数，我猜隐式转换正在发生?

Also here I did not use any TO_DATE function, I guess implicit conversion is taking place?

推荐答案

字符串到日期转换规则 允许附加格式规则(不应用任何其他修饰符).所以:

The String-to-Date Conversion Rules allow additional formatting rules (without any other modifiers being applied). So:

  • MM 也匹配 MON 和 MONTH;
  • MON 匹配 MONTH(反之亦然)；
  • RR 匹配 RRRR;和
  • 标点符号是可选的.
  • MM also matches MON and MONTH;
  • MON matches MONTH (and vice versa);
  • RR matches RRRR; and
  • The punctuation is optional.

所以:

SELECT TO_DATE( '10AUGUST2016', 'DD-MM-RR'    ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUGUST2016', 'DD-MON-RR'   ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUGUST2016', 'DD-MONTH-RR' ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MM-RR'    ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MON-RR'   ) FROM DUAL UNION ALL
SELECT TO_DATE( '10AUG2016',    'DD-MONTH-RR' ) FROM DUAL;

所有生成日期2016-08-10T00:00:00.

您可以使用FX 格式模型

You can prevent this by using the FX format model

格式准确.此修饰符为 TO_DATE 函数的字符参数和日期时间格式模型指定精确匹配:

FX

Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:

  • 字符参数中的标点和引用文本必须与格式模型的相应部分完全匹配(大小写除外).

  • Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.

字符参数不能有多余的空格.如果没有 FX，Oracle 会忽略额外的空格.

The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.

字符参数中的数字数据必须与格式模型中的相应元素具有相同的位数.如果没有 FX，字符参数中的数字可以省略前导零.

Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes.

当启用 FX 时，您也可以使用 FM 修饰符禁用前导零检查.

When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.

如果字符参数的任何部分违反了这些条件中的任何一个，那么 Oracle 将返回一条错误消息.

If any portion of the character argument violates any of these conditions, then Oracle returns an error message.

那么:

SELECT TO_DATE( '10-AUGUST-2016', 'FXDD-MM-RR'    ) FROM DUAL;

给出:ORA-01858:在需要数字的地方找到了一个非数字字符，并且只会在找到精确模式匹配的地方匹配(尽管 RR 会仍然匹配RRRR).

Gives: ORA-01858: a non-numeric character was found where a numeric was expected and would only match where an exact pattern match is found (although RR will still match RRRR).

我猜隐式转换正在发生?

I guess implicit conversion is taking place?

是的，oracle 隐式使用 TO_DATE( '10AUGUST2016', NLS_DATE_FORMAT ) 进行转换.

Yes, oracle is implicitly using TO_DATE( '10AUGUST2016', NLS_DATE_FORMAT ) to do the conversion.

如果您使用:

ALTER SESSION SET NLS_DATE_FORMAT = 'FXDD-MM-RR';

那么你的插入就会失败.

Then your insert will fail.

相关文章