• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

Find a Min_date and Max_date closer to a given date - oracle

I have table where there is a Begin_Effective_Date and End_Effective_date. My task is If I  have given a date , I should be able to tell ,  the closest Begin_Effective_Date  and closest End_Effective_date

Begin_eff_date :- 01/15/2008
End eff_date :- 02/05/2008

If I key in 02-01-2008, I am expecting the date range to be between 01/15/2008 and 02/05/2008

3 Solutions
Jinesh KamdarCommented:
Try this.
SELECT B.begin_eff_date, E.end_eff_date FROM
(SELECT begin_eff_date,
        ABS(begin_eff_date - TO_DATE('02-01-2008','MM-DD-YYYY')) AS closest_begin_date
 FROM   table
 ORDER BY closest_begin_date) B,
(SELECT end_eff_date,
        ABS(end_eff_date - TO_DATE('02-01-2008','MM-DD-YYYY')) AS closest_end_date
 FROM   table
 ORDER BY closest_end_date) E,
WHERE rownum = 1;

Open in new window

select a.bdate,b.edate
from (select max(bdate) bdate
from table
where bdate  <= to_date('2/1/2008','mm/dd/rrrr')) a,
select min(edate) edate
from table
where edate  >= to_date('2/1/2008','mm/dd/rrrr')
) b
select TheDate
  from TheTable
 where abs(datediff('d',TheDate,somedate))
     = ( select min(
           from TheTable )
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Jinesh KamdarCommented:
Note that in my post I do not check that begin-date is less than the date to be compared, so if u key in
1-Feb-2008 for comparison and the table has 2 begin-dates as 2-Feb-2008 and 30-Jan-2008, then my query will show 2-Feb-2008 instead of the latter since 2-Feb-2008 is closer to 1-Feb-2008 (i.e. difference is only 1 day) than 30-Jan-2008 is (difference is 2 days) even though 2-Feb-2008 comes after 1-Feb-2008. If that's what u want, then use my query else go for sventhan's version.
Jinesh KamdarCommented:
@sonicefu: Does DATEDIFF exist in Oracle as well ?
akp007Author Commented:
sventhan soluntion works for me. BUt I will try the other ones also. Thanks for the repsonse
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now