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

# 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

ex:-
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

thanks
0
akp007
3 Solutions

Commented:
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;
``````
0

Commented:
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
0

Commented:
select TheDate
from TheTable
where abs(datediff('d',TheDate,somedate))
= ( select min(
abs(datediff('d',TheDate,somedate))
)
from TheTable )
0

Commented:
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.
0

Commented:
@sonicefu: Does DATEDIFF exist in Oracle as well ?
0

Author Commented:
sventhan soluntion works for me. BUt I will try the other ones also. Thanks for the repsonse
0
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.