ORA-01481: invalid number format model in oracle insert query

Question

I have below query where i am getting error as

ORA-01481: invalid number format model
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.

I am using this query in stored procedure. The day_id column is number data type and the value stored in this column as YYYYMMDD. The month_id column is also number data type and i want to stored the value in this column as YYYYMM

INSERT INTO TEST_CHECK(MONTH_ID) VALUES
 (to_char(REC.day_id, 'YYYYMM'));

`to_char()` returns a `VARCHAR` not a number so you can't store that into a `number` column — , Jan 23 '18 at 10:40
If all you want to do is lose the last two digits, isn't that `trunc(day_id/100)`? — William Robertson, Jan 23 '18 at 10:53

Aleksej · Accepted Answer · 2018-01-23T10:42:53.437

You're applying a conversion to a number as if it was a date, trying to use the result (a string) as a number.

SQL> create table TEST_CHECK(MONTH_ID number);

Table created.

SQL> INSERT INTO TEST_CHECK(MONTH_ID) VALUES(to_char(20180101, 'YYYYMM'));
INSERT INTO TEST_CHECK(MONTH_ID) VALUES(to_char(20180101, 'YYYYMM'))
                                                             *
ERROR at line 1:
ORA-01481: invalid number format model

You may need something like:

SQL> INSERT INTO TEST_CHECK(MONTH_ID) VALUES(substr(to_char(20180101), 1, 6));

1 row created.

I would remember that storing dates and months in such a way is not a good idea.

Barbaros Özhan · Answer 2 · 2018-01-23T10:44:50.163

Use

INSERT INTO TEST_CHECK(MONTH_ID) VALUES
 (substr(REC.day_id, 1, 6));

instead, since both day_id and month_id are numbers.

If day_id were in date format you could make such a conversion to char but this not the case.

You may apply to_char conversion for REC.day_id as to_char(REC.day_id) but if there's no non-numeric character, oracle considers number as char implicitly without to_char, during a string operation such as substr.

ORA-01481: invalid number format model in oracle insert query

2 Answers2