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?

Snaggy

I don't know PL/SQL (as used by Oracle) but you should be able to use ISO-8601 (yyyy-MM-dd) as a format. Have you tried using dashes instead of slashes? – Castorena

@dai, thanks for the suggestion. Unfortunately I get same error even with dashes (I tried: without dashes as input, with dashes as input, and also dashes for the TO_CHAR parameter) – Snaggy

TO_CHAR(:fromDate, 'YYYY/MM/DD') is wrong. 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. – Brahmanism

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

Brahmanism

This taught me several things while at the same time solving the problem. It should be possible to up-vote twice ;) – Snaggy

Answer 2

I hope this solution helps.

There are too many unnecessary conversions.

I have simulated the same scenario in my system,

Here CREATED_TIME is a column of datatype timestamp(6).

Anesthesiologist

@Anesthesiologist ..Where is the answer here? – Brahmanism

@LalitKumarB it might not be the answer for you. But I have guided him in the right direction. Answer is too simple, don't do unnecessary things in SQL, keep it simple. Also, you have provided the answer, if my answer was wrong the the OP would never have commented my answer is correct. – Anesthesiologist