subtracting dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: subtracting dates

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    subtracting dates

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    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,
    ____________________
    Pete

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    ANSI date literals are also handy for this sort of thing:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width