subtracting dates

[javamaid]

how to subtract
sysdate-'09-FEB-80'
SYSDATE-HIRE_DATE works just fine
but sysdate-an actual date gives
invalid identifier error

SQL> select sysdate-'09-feb-90'
2 from dual;
select sysdate-'09-feb-90'
*
ERROR at line 1:
ORA-01722: invalid number

This too gives error
1 select sysdate-to_date('09-02-80',DD-MON-YY)
2 from dual;

ERROR at line 1:
ORA-00904: "YY": invalid identifier

New to SQL.Thanks for the help

[rigatoni]

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,

[WilliamR]

ANSI date literals are also handy for this sort of thing:

SELECT sysdate - DATE '1990-02-02' FROM dual;