SQL date conversion results to "invalid number format model parameter."

Question

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.

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

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?

Answer 1

Root Cause:

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

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

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

Since you are dealing with TIMESTAMP you could use 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_CHAR only for display.

Modify your filter predicate as:

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

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

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_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