Oracle – Date Type Implicit Conversion Explained

datatypesoracletype conversion

When does oracle implicitly converts date type? on what parameters does it depend?

I am running the following:

SELECT SYSDATE - '01-01-01' FROM DUAL

and got :

SELECT SYSDATE - '01-01-01' FROM DUAL
                  *
ERROR at line 1:
ORA-01722: invalid number

why Oracle not converting varchar '01-01-01' to date ?

nls_date_format I have set to 'DD-MM-RR'

Best Answer

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number.
Nls_date_format parameter helps to implicitly convert the varchar to a date type.
Example:

SQL>INSERT INTO tbl values ('01-02-16');
SQL>SELECT ADD_MONTHS('01-02-16',2)

In the above example implicit type conversion happens since we have set NLS_DATE_FORMAT . In your case it was trying to extract number(Or you could tell explicitly) from SYSDATE but found string not date.
Example:

SQL>SELECT SYSDATE-'01' FROM dual;

Here this works perfectly and returns the date subtracting 1 day from the SYSDATE because it was able to convert the string to number.
The best way is to explicitly convert the varchar to a date type. You can use the following query to subtract date from sysdate(assuming you have set NLS_DATE_FORMAT=DD-MM-RR):

SQL>SELECT SYSDATE-TO_DATE ('01-01-01');

Oracle recommends to use explicit type conversion.

Related Question