October 23rd, 2003, 10:03 AM
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.
October 24th, 2003, 11:47 AM
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
October 24th, 2003, 12:34 PM
how about using add_months
select add_months(sysdate,-36) from dual
October 24th, 2003, 12:53 PM
In Oracle 9i the following will do it:
select sysdate - to_yminterval('03-00') from dual
October 24th, 2003, 01:13 PM
Thank you. I appreciate your help.