Why is there an issue with the ADD_MONTHS function in Oracle SQL

Question

I am using the online APEX Oracle DB for some training and have ran into a problem with one of the functions that has me scratching my head.

I have done some testing and have been able to get it to work the long hand way but not the shortened syntax. Using the shortened syntax returns an error which I can't figure out. Any help would be appreciated.

What I have tried:

select add_months('2015/10/12', 4) from dual returns an error:

ORA-01846: not a valid day of the week

What I got to work:

select add_months(to_date('2015/10/12', 'yyyy/mm/dd'), 4) from dual

when I use the add_months(to_date()) format

I get back the correct result of, Friday, 12 February, 2016

If I use the add_months(arg1, arg2) it returns the error.

Answer 1

I was able to get this figured out. I ran systimestamp and this was what was returned :

Saturday, 02 February, 2019.

I then took and plugged in this into the function I was having trouble with

select add_months(systimestamp, 4) from dual and it returned the correct result :

Sunday, 02 June, 2019 depending on my date formatting.

Answer 2

Find you current NLS_DATE_FORMAT with below SQL

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

Basically when you pass a date as varchar2 string, this format is used for the implicit date conversion.

So, instead of dealing with the default date format, we should always use explicit date conversion using TO_DATE()

Answer 3

add_months expects date but you are providing VARCHAR without formatting which causes unexpected errors.

Basically oracle cannot implicitly converts given format to date