SQL 日期转换结果为“无效的数字格式模型参数”。

我必须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

查询失败并显示以下错误消息：

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.

我对变量的输入fromDate and toDate只是日期字符串，例如20111010等等。我也尝试过更具体的时间（与表中的格式相同），但这似乎不是问题。

在数据库中CREATE_TIME列是TIMESTAMP(6)类型，例如一个样本是2011/12/19 08:04:42

任何想法为什么会弹出这个错误？

根本原因：

您正在转换一个NUMBER to STRING，假设它是DATE. 20111010不是日期，而是数字。还，'20111010'不是日期，而是字符串。他们是完全不同的。

• 20111010 - NUMBER
• '20111010' - STRING
• TO_DATE('20111010','YYYYMMDD') - DATE

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

来到您的查询：

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

您不必要地使转换和格式化变得复杂。

The 时间戳数据类型是一个扩展DATE数据类型。除了 DATE 数据类型的日期时间元素之外，TIMESTAMP 数据类型还将秒的小数部分保存为 0 到 9 位小数之间的精度，默认值为 6。

在数据库中，CREATE_TIME列是TIMESTAMP(6)类型，例如一个样本是2011/12/19 08:04:42

既然你正在处理时间戳你可以用TO_TIMESTAMP.

在做一个日期/时间戳算术，您应该保留数据类型不变，而不是将其转换为string。你需要使用TO_CHAR仅适用于display.

将您的过滤谓词修改为：

WHERE CREATE_TIME 
BETWEEN TO_TIMESTAMP(:fromDate, 'YYYY/MM/DD') 
AND TO_TIMESTAMP(:toDate, 'YYYY/MM/DD')

Above, :fromDate and :toDate应该是一个string而不是一个number.

例如，

SQL> SELECT to_timestamp('20111010', 'YYYYMMDD') FROM dual;

TO_TIMESTAMP('20111010','YYYYMMDD')
-----------------------------------------------------------
10-OCT-11 12.00.00.000000000 AM

Or, use TO_CHAR首先convert the number into string:

SQL> SELECT to_timestamp(TO_CHAR(20111010), 'YYYYMMDD') FROM dual;

TO_TIMESTAMP(TO_CHAR(20111010),'YYYYMMDD')
------------------------------------------------------------------
10-OCT-11 12.00.00.000000000 AM