akp007
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@sonicefu: Does DATEDIFF exist in Oracle as well ?
ASKER
sventhan soluntion works for me. BUt I will try the other ones also. Thanks for the repsonse
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.