Get the records nearest to GETDATE() in SQL Server

In this post i would like to show you different ways to find the rows nearest to the GetDate() in SQL Server.

Method 1, is based on calculating the difference interims of days between GetDate() and HireDate and then take the absolute number using ABS. Order these results in ascending (ASC)order by using ORDER BY Clauase. Finally, filter the results using TOP 1 WITH TIES. WITH TIES is used to select all the records when there is a tie.

Method 1:
=======

SELECT TOP 1 WITH TIES
BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS [Current_Date]
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,GETDATE(),HireDate));

Method 2, is based on using Group Functions. Where MAX function is used to take the recent date and based on this date extracted all the other information.

Method 2:
=======

SELECT
BusinessEntityID,
LoginID,
HireDate
FROM HumanResources.Employee
WHERE HireDate = (SELECT MAX(HireDate) FROM HumanResources.Employee)

Method 3, is based on Analytical Functions in SQL Server. Where RANK function is used to
along with the OVER BY to calculate the difference between GetDate and HireDate. This logic is same as in Method 1.

Method 3:
=======

SELECT V.* FROM
(SELECT
BusinessEntityID,
LoginID,
HireDate,
RANK() OVER (ORDER BY ABS(DATEDIFF(DAY,GETDATE(),HireDate))) AS RecID
FROM HumanResources.Employee ) V
WHERE V.RecID = 1

Please let me know if you have any other ways to wirte these queries.....

Reference : Vijaya Kadiyala (http://www.dotnetvj.com/)