SQL日期转换结果为“无效的数字格式模型参数". [英] SQL date conversion results to "invalid number format model parameter."

问题描述

我必须select来自Oracle 11g数据库的一些数据，但是我似乎无法弄清楚为什么以下select查询失败的原因:

I have to select some data from Oracle 11g database, but I can't seem to figure out why following select query is failing:

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.

我输入的变量fromDate和toDate只是日期字符串，例如20111010等.我也尝试了更具体的时间(与表中的格式相同)，但这似乎不是问题.

My input for the variables fromDate and toDate are just date strings such as 20111010 etc. I have tried also more specific time (same format as in the table), but that doesn't seem to be the problem..

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

In the database the CREATE_TIME column is TIMESTAMP(6) type, and for example one sample is 2011/12/19 08:04:42

为什么会弹出错误提示?

Any ideas why this is error pops up?

推荐答案

根本原因:

您假设将 NUMBER 转换为 STRING ，假设它是 DATE . 20111010不是DATE，而是NUMBER.另外，'20111010'不是DATE，而是STRING.它们是完全不同的.

You are converting a NUMBER to STRING, assuming it to be DATE. 20111010 is not a DATE, it is a NUMBER. Also, '20111010' is not a DATE, it is a STRING. They are completely different.

• 20111010- NUMBER
• '20111010'- STRING
• TO_DATE('20111010','YYYYMMDD')-日期

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

错误:

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

您不必要地使转换和格式复杂化.

You are unnecessarily complicating the conversion and formatting.

TIMESTAMP 数据类型是 DATE 数据类型的扩展.除了DATE数据类型的datetime元素外，TIMESTAMP数据类型还保留一秒的精度，精确到0到9个小数位，默认值为6.

The TIMESTAMP datatype is an extension on the DATE datatype. 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.

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

In the database the CREATE_TIME column is TIMESTAMP(6) type, and for example one sample is 2011/12/19 08:04:42

由于您要处理 TIMESTAMP ，因此可以使用 TO_TIMESTAMP .

Since you are dealing with TIMESTAMP you could use TO_TIMESTAMP.

在执行 DATE/TIMESTAMP算术时，您应保持数据类型不变，并且不要将其转换为 string .您只需将 TO_CHAR 用于显示.

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_CHAR only for display.

将过滤谓词修改为:

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

上方，:fromDate和:toDate应该是字符串，而不是数字.

Above, :fromDate and :toDate should be a string and not a number.

例如，

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

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

或者，使用 TO_CHAR 首先将数字转换为字符串:

Or, use TO_CHAR to first 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

这篇关于SQL日期转换结果为“无效的数字格式模型参数".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！