ORA-01858: a non-numeric character was found where a numeric was expected

An application related SQL query with following line

AND TO_DATE(DEP_DATE, 'DD-MM-YYYY') = TO_DATE(SYSDATE+360 , 'DD-MM-YYYY')

was failing on some databases while succeeding on some. Though the logic seems at odd (why to_date a date type column) the issue was why it was failing on some databases with ORA-01858 while succeeding in others when executed using SQLPlus. Even on the database the query was failing it was possible to execute the query using JDBC (reason it was working on the application without the error).
After comparing and contrasting the databases the query succeeded and failed it came down to NLS_DATE_FORMAT parameter set on the bash shell environment on some of the databases.

echo $NLS_LANG
English_United Kingdom.AL32UTF8
$ export NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'
$ sqlplus / as sysdba
SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;
select to_date(sysdate,'YYYY-MM-DD') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

$ unset NLS_DATE_FORMAT
$ sqlplus / as sysdba

SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;

TO_DATE(SYSDATE,'Y
------------------
12-NOV-29

It is possible to run the to_date with NLS_DATE_FORMAT set but this must match the format string.

$ export NLS_DATE_FORMAT='DD-MM-YYYY'
$ sqlplus / as sysdba

--different format to that of NLS_DATE_FORMAT
SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;
select to_date(sysdate,'YYYY-MM-DD') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

--same format as the NLS_DATE_FORMAT
SQL> select to_date(sysdate,'DD-MM-YYYY') from dual;

TO_DATE(SY
----------
29-11-2012

NLS_DATE_FORMAT comes into play if NLS_LANG is also set. If NLS_LANG is unset then NLS_DATE_FORMAT is ignored.

Useful metalink notes
RMAN Backup Fails With RMAN-03009 ORA-01858 ORA-01861 [ID 744047.1]
Query using TO_DATE TO_CHAR failing with ORA-01858 or ORA-01843 [ID 790098.1]
The Priority of NLS Parameters Explained (Where To Define NLS Parameters) [ID 241047.1]
OERR: ORA 1858 "a non-numeric character was found where a numeric was expected" [ID 19182.1]
How To Set a NLS Session Parameter At Database Or Schema Level For All Connections? [ID 251044.1]