#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    5
    Rep Power
    0

    System Date minus three years?


    Would anyone be able to help, please?I have a table called PS_N_ACTIVE (ORACLE DB) and I wanted to subtract three years from N_ACT_DUTY_SVC_DT. The date format is MM-DD-YYYY. I can't find the correct SQL statement to insert. Basically, I'm using several joins and top of stack logic but I wanted to be able to insert a statement something to the effect: ...."where ACT.N_ACT_DUTY_SVC_DT = SYSDATE MINUS 3 YEARS" and be able to have the output or results in the MM-DD-YYYY format.

    I picked up a reference book The practical SQL Handbook but couldn't find any reference to what I wanted to do.

    Appreciate any help. Thank you.
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    Moved to appropriate forum.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    how about using add_months
    ie.
    PHP Code:
    select add_months(sysdate,-36from dual 
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    In Oracle 9i the following will do it:

    select sysdate - to_yminterval('03-00') from dual

    Cheers,
    Dan
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    5
    Rep Power
    0
    Thank you. I appreciate your help.

IMN logo majestic logo threadwatch logo seochat tools logo