Printable View
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
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,
ANSI date literals are also handy for this sort of thing:
SELECT sysdate - DATE '1990-02-02' FROM dual;