How to find record for Nearest current time in sql sever

Hi,

I am having three record in my table.

1      Student        03/06/2012       04:00 PM  
2    Staff     30/05/2012     02:58 PM  
3    Others     03/06/2012     10:00 AM    

In this table have two record on same date Student and Staff ( 03/06/2012 ) but time field is different. Student is  04:00 PM and Others is 10:00 AM.

How to get nearest time record using current time.

ex. my current time is 8.00 AM or 12.00 PM get record no 3. because its time 10.00 AM. It is only nearest to current time. If my current time is 03.00 PM or 06.00 PM then get record no 1. because its time 04.00 PM. It is only nearest to current time.

Solution

SELECT Top 1 * FROM tablename WHERE CONVERT(VARCHAR(10),datetimecolumnname,103) ='01/06/2012' order by ABS(DATEDIFF(minute, datetimecolumnname, GETDATE()))


CONVERT(VARCHAR(10),datetimecolumnname,103) ='01/06/2012'
------------------------------------------------------------------------------------
It will get a record on 03/06/2012

order by ABS(DATEDIFF(minute, datetimecolumnname, GETDATE()))
----------------------------------------------------------------------------------------
It will get a closest time from current time.

SELECT Top 1
------------------
it will get a first record of after performing these conditions.