Formatting a DATE type in an Oracle database to only accept MMDD

This works

SELECT MONTH(CURDATE());

With the final query like

SELECT    *
FROM      tbl_orders
WHERE     MONTH(date) = @month
AND       YEAR(date) = @year;

Here @month and @year are numeric values from the administrator. Defaults could be

SET @month = MONTH(CURDATE());
SET @year = YEAR(CURDATE);

This is the quick and dirty solution applicable for a small table. If performance is an issue, consider putting a binary tree index on the date column or breaking it down into (month, year) with a hash index.

Binary tree index allows comparison like so

WHERE     date >= CONCAT(@year, '-', @month, '-', '01')
AND       date <  CONCAT(@year, '-', @month+1, '-', '01');

But this does not work when @month=12 unless you do some complicated date math, which can be done elsewhere. Note that you can do concatenation elsewhere too, to enable query caching.

With hash index

SELECT    *
FROM      tbl_orders
WHERE     month = @month
AND       year  = @year;

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.