You were right when you suspected you needed to use the to_date function. You just used it incorrectly in the second example. Here's some examples of what you wanted. Be careful how you specify the date format. MON indicates that you are using a 3-letter abbreviation for the month while MM indicates you are using the 2-digit numeric representation

SQL> select sysdate - to_date('01-jan-2006')
2 from dual;

SYSDATE-TO_DATE('01-JAN-2006')
------------------------------
30.5238079


1 select sysdate-to_date('09-02-80','DD-MM-YY')
2* from dual
SQL> /

SYSDATE-TO_DATE('09-02-80','DD-MM-YY')
--------------------------------------
-27036.475


1 select sysdate-to_date('09-02-1980','DD-MM-YYYY')
2* from dual
SQL>
SQL> /

SYSDATE-TO_DATE('09-02-1980','DD-MM-YYYY')
------------------------------------------
9488.52541


hope that helps,