无法了解值如何隐式转换为日期格式 [英] Unable to understand how value converted to date format implicitly

问题描述

当我检查我的NLS_DATE_FORMAT是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

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

FX

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

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

• 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.

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

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.

如果character参数的任何部分违反了这些条件中的任何一个，则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: a non-numeric character was found where a numeric was expected，并且仅在找到完全匹配的模式时才匹配(尽管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';

然后插入将失败.

这篇关于无法了解值如何隐式转换为日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！