Date range from table [message #638154] |
Thu, 04 June 2015 07:16 |
|
mach_9
Messages: 3 Registered: June 2015 Location: Ottawa
|
Junior Member |
|
|
I am trying to get a date range from one of my tables, which will provide me expired data (expiry date less than sysdate) and data that expired 5 years or less.
SQL:
select * from rating_table
where expiry_date <= sysdate
ORDER by expiry_date DESC;
Sample Data:
Record: #1 - expiry date 2015-06-01
Record: #2 - expiry date 2015-05-01
-This will give me all expired entries(26,743).
If today's date is June 4th, 2015, I would like all the records that are expired within a 5 yr. period, June 4th, 2010.
I have tried:
select * from rating_table
where trunc(expiry_date) <= trunc(SYSDATE - 60)
ORDER by expiry_date DESC;
-no luck.
Not sure how to write the SQL for this, any help would be much appreciated.
Thank you,
William
|
|
|
|
|
|
|
|
Re: Date range from table [message #638180 is a reply to message #638166] |
Fri, 05 June 2015 04:42 |
|
Littlefoot
Messages: 21805 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
From my point of view, it is not the additional condition you used (expiry_date < trunc(sysdate) + 1), but wrong assumption.
Previously: where trunc(expiry_date) <= add_months(trunc(sysdate), -60)
Now : where expiry_date >= add_months(trunc(sysdate), -60)
^
|
This!
Anyway, if you're happy with the solution, so am I.
[Updated on: Fri, 05 June 2015 04:42] Report message to a moderator
|
|
|