0

I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and starting from 2 select statements:

select to_char('29.01.2018 00:00:00', 'DD.MM.YYYY HH24:MI:SS') from dual;

vs.

select to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS') from dual;

I am asking why 1st select returns error: ORA-01722: invalid number?

Why 1st select, in order to return the expected result needs to be written as: select to_char(to_date('29.01.2018 00:00:00', 'DD.MM.YYYY HH24:MI:SS')) from dual;?

... and extrapolating from here I have another 2 situations related to 1st select:

  1. when it populates a VARCHAR2 field from a record it works just fine.
  2. when it populated a VARCHAR2 field from a table of records it returns above error message?

Thank you very much,

mikcutu
  • 1,013
  • 2
  • 17
  • 34
  • 1
    There's no use in converting a string to yet another string (1st example). TO_DATE would make more sense. – Littlefoot Mar 08 '18 at 12:23

2 Answers2

2

I am asking why 1st select returns error: ORA-01722: invalid number?

There are three versions of to_char() which take different data type arguments; one for number, one for datetime and one for char.

If you only passed your string, without the second format-model argument:

to_char('29.01.2018 00:00:00')

then it would use the char version and not complain. But you are supplying a second argument, so only the number and datetime versions might be valid.

Neither of those accepts text literals as the first argument. But Oracle generally tries to be helpful and allows a lot of implicit conversion. (This isn't always a good thing.)

In this case it assumes you meant the first version and are trying to pass in a number, and attempts to implicitly convert the string you gave to a number. As it can't be converted, you get the ORA-01722 error.

When you modify it to do:

to_char(to_date('29.01.2018 00:00:00', 'DD.MM.YYYY HH24:MI:SS'))

you're explicitly converting the string to a datetime, so it knows you want to use that version of the function, not the number version, since that is the data type of the first argument when the function is actually called..

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

to_char('29.01.2018 00:00:00', 'DD.MM.YYYY HH24:MI:SS') is useless. '29.01.2018 00:00:00' is a STRING, not a DATE. It does not make any sense to convert a string into a string.

In order to output a date in certain format with TO_CHAR() you have to provide a DATE (or TIMESTAMP) value. SYSDATE is a DATE value.

One way to provide a DATE is using TO_DATE() function as given in your question.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110