SQL 日期转换结果为“无效的数字格式模型参数”。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33687413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL date conversion results to "invalid number format model parameter."
提问by julumme
I have to selectsome data from Oracle 11gdatabase, but I can't seem to figure out why following selectquery is failing:
我必须select从Oracle 11g数据库中获取一些数据，但我似乎无法弄清楚以下select查询失败的原因：
SELECT
INFO_ID,
INFO_DETAIL,
IMPORTANT_FLG,
DELETE_FLG,
CREATE_TIME,
DISPLAY_ORDER
FROM TABLE_NAME
WHERE TO_DATE(TO_CHAR(CREATE_TIME, 'YYYY/MM/DD'), 'YYYY/MM/DD')
BETWEEN TO_DATE(TO_CHAR(:fromDate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AND TO_DATE(TO_CHAR(:toDate, 'YYYY/MM/DD'), 'YYYY/MM/DD')
ORDER BY IMPORTANT_FLG DESC NULLS LAST , DISPLAY_ORDER ASC NULLS LAST, CREATE_TIME DESC, INFO_ID ASC
The query is failing with following error message:
查询失败并显示以下错误消息：
ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
My input for the variables fromDateand toDateare just date strings such as 20111010etc. I have tried also more specific time (same format as in the table), but that doesn't seem to be the problem..
我对变量输入fromDate和toDate只是日期字符串，如20111010等等。我也曾尝试更具体的时间（相同的格式如表），但似乎并没有被这个问题..
In the database the CREATE_TIMEcolumn is TIMESTAMP(6)type, and for example one sample is 2011/12/19 08:04:42
在数据库中，CREATE_TIME列是TIMESTAMP(6)类型，例如一个样本是2011/12/19 08:04:42
Any ideas why this is error pops up?
任何想法为什么会弹出这个错误？
回答by Lalit Kumar B
Root Cause:
根本原因：
You are converting a NUMBERto STRING, assuming it to be DATE. 20111010is not a DATE, it is a NUMBER. Also, '20111010'is not a DATE, it is a STRING. They are completely different.
您正在将NUMBER转换为STRING，假设它是DATE。20111010不是日期，而是数字。此外，'20111010'不是日期，而是字符串。他们是完全不同的。
20111010- NUMBER'20111010'- STRINGTO_DATE('20111010','YYYYMMDD')- DATE
20111010-号码'20111010'-字符串TO_DATE('20111010','YYYYMMDD')-日期
Error:
错误：
SQL> SELECT TO_CHAR(20111010, 'YYYY/MM/DD') FROM dual;
SELECT TO_CHAR(20111010, 'YYYY/MM/DD') FROM dual
*
ERROR at line 1:
ORA-01481: invalid number format model
Coming to your query:
来到您的查询：
WHERE TO_DATE(TO_CHAR(CREATE_TIME, 'YYYY/MM/DD'), 'YYYY/MM/DD')
BETWEEN TO_DATE(TO_CHAR(:fromDate, 'YYYY/MM/DD'), 'YYYY/MM/DD')
AND TO_DATE(TO_CHAR(:toDate, 'YYYY/MM/DD'), 'YYYY/MM/DD')
You are unnecessarily complicating the conversion and formatting.
您不必要地使转换和格式复杂化。
The TIMESTAMPdatatype is an extension on the DATEdatatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6.
该TIMESTAMP数据类型是在一个扩展DATE数据类型。除了 DATE 数据类型的日期时间元素之外，TIMESTAMP 数据类型还保存秒的小数部分，精度在 0 到 9 个小数位之间，默认值为 6。
In the database the CREATE_TIME column is TIMESTAMP(6) type, and for example one sample is 2011/12/19 08:04:42
在数据库中，CREATE_TIME 列是 TIMESTAMP(6) 类型，例如一个样本是 2011/12/19 08:04:42
Since you are dealing with TIMESTAMPyou could use TO_TIMESTAMP.
由于您正在处理TIMESTAMP，因此您可以使用TO_TIMESTAMP。
While doing a DATE/TIMESTAMP arithmetic, you should leave the data type as it is and not convert it into string. You need to useTO_CHARonly for display.
在执行DATE/TIMESTAMP 算术时，您应该保持数据类型不变，不要将其转换为string。您只需要将TO_CHAR用于display。
Modify your filter predicate as:
将过滤谓词修改为：
WHERE CREATE_TIME
BETWEEN TO_TIMESTAMP(:fromDate, 'YYYY/MM/DD')
AND TO_TIMESTAMP(:toDate, 'YYYY/MM/DD')
Above, :fromDateand :toDateshould be a stringand not a number.
上面，:fromDate并且:toDate应该是一个字符串而不是一个数字。
For example,
例如，
SQL> SELECT to_timestamp('20111010', 'YYYYMMDD') FROM dual;
TO_TIMESTAMP('20111010','YYYYMMDD')
-----------------------------------------------------------
10-OCT-11 12.00.00.000000000 AM
Or, use TO_CHARto first convertthe numberinto string:
或者，使用TO_CHAR先转换的数量成字符串：
SQL> SELECT to_timestamp(TO_CHAR(20111010), 'YYYYMMDD') FROM dual;
TO_TIMESTAMP(TO_CHAR(20111010),'YYYYMMDD')
------------------------------------------------------------------
10-OCT-11 12.00.00.000000000 AM
回答by Jeethu
I hope this solution helps.
我希望这个解决方案有帮助。
There are too many unnecessary conversions.
有太多不必要的转换。
I have simulated the same scenario in my system, 
我在我的系统中模拟了相同的场景，
Here CREATED_TIMEis a column of datatype timestamp(6).
这CREATED_TIME是一列数据类型timestamp(6)。
