2

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'
1
  • 1
    As JSapkota mentioned, Oracle recommends using explicit conversions over implicit conversions. Tom Kyte has a good article on why implicit conversions are bad here (it opens the code up to logic bombs and even SQL Injection attacks): oracle.com/technetwork/issue-archive/2014/14-sep/… Feb 16, 2016 at 16:02

1 Answer 1

2

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.

1
  • @kupa my pleasure :)
    – atokpas
    Feb 16, 2016 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.