How to find the nearest date?

Question

I have a schema which looks something like this:

CREATE TABLE [dbo].[CdCl](
    [Id] [int] NOT NULL,
    [SubId] [varchar](15) NULL,
    [ReadTime] [datetime] NOT NULL,
    [ActualPower] [real] NOT NULL
)

And data that looks as follows:

I'm working on a query that does the following, but don't quite know the best way to proceed:

1. If a Where clause contains a ReadTime of '5/1/2013 10:34:09', it would return the record highlighted, because it is an exact match.

2. If a Where clause contains a ReadTime '5/1/2013 10:34:11', it would return the record highlighted because that is the nearest match.

3. If a Where clause contains a ReadTime of '5/1/2013 10:34:14', it would return the record containing '5/1/2013 10:34:16' because that is the nearest match.

I'm using SQL Server 2012 and would be fine with a 2012 specific query.

Answer 1

Something like this:

select top 1 *
from CdCl
order by abs(datediff(ms, ReadTime, <yourdatetime>))

Should be able to adapt that to your needs