sql – 无法理解隐式地如何将值转换为日期格式

考虑到带有日期列的mytable,当我执行以下语句时,它会抛出错误“ORA-01840：输入值不够长,不适合日期格式”,这是合理可以理解的.

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

那么为什么后面的语句在输入大小大于默认DATE格式大小的情况下有效.

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

另外在这里我没有使用任何TO_DATE函数,我猜隐式转换正在发生？

解决方法

> MM也匹配MON和MONTH;
> MON匹配MONTH(反之亦然);
> RR匹配RRRR;和
>标点符号是可选的.

所以：

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 format model来防止这种情况

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.

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

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

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

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

I guess implicit conversion is taking place?

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

如果您使用：

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

然后你的插入将失败.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!