Query on Dates

[fox12]

I need to retrieve records based on the fiscal year. In the table, there is a starting_date field and a ending_date field in the format 'MM/DD/YY'. How can I select records since the last 5 years?

Thanks

 

[fox12]

Oops. The starting date(month/day) is always 10/01/YYYY.

 

[SantaMufasa]

Fox,

I cannot tell whether your "last 5 years" criterion is for starting_date or ending_date, but here is some code that will show records if either the starting_date or ending_date is within the last 5 years:

select * from table_name
WHERE starting_date between add_months(sysdate,-5*12) and sysdate
   OR ending_date between add_months(sysdate,-5*12) and sysdate;

Let us know how some variation of this theme works for you.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[fox12]

Mufasa,

Thanks a lot. That works for me.

Fox12

 

[DeepDiverMom]

I like to learn and pick up what I can from Tek-Tips, so I spend a great deal of time here. I also tend to become well acquainted with people’s posting and response habits.

I’ve noticed, Fox12, that since your joined Tek-Tips on Jan 18, 2005, you have started 19 threads across 6 different forums. Here in the Oracle 9i forum, your 5 threads have resulted in spectacular help from Sem, Carp, SantaMufasa, dbomrrsm, and lewisp. They have unselfishly invested collectively what look to be hours of their time to help you solve your problems, saving you additional countless hours of your time and thousands of dollars in consulting fees.

Strangely, in not one of your 19 threads (including those here in Oracle 9i forum) have you voted a Star for any of their helpful responses. I don’t know if you are aware that the currency here on Tek-Tips that acknowledges the time these problem solvers invest is “Purple Stars”. Awarding the Stars doesn’t cost you anything, they are a great way to show your sincere appreciation, and generally, the recipients are grateful for your acknowledgment. All it takes is your clicking on the “Thank <submitter> for this valuable post” link. I’ll bet the responders, above, will be more eager to help you in the future if you do. It’s not too late to go back and show your gratitude on your old posts, either.

Mom