考虑到带有日期列的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_DATEfunction:
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
FXis enabled,you can disable this check for leading zeroes by using theFMmodifier 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';
然后你的插入将失败.
