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:

enter image description here

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

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.
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.
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.

Possible duplicate: http://stackoverflow.com/questions/4649665/t-sql-select-by-nearest-date-and-grouped-by-id?rq=1 — Esoteric Screen Name, May 01 '13 at 14:50

score 8 · Accepted Answer · answered May 01 '13 at 14:48

8

Something like this:

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

Should be able to adapt that to your needs

answered May 01 '13 at 14:48

Derek

21,828
7
53
61

Gotta love simple elegant answers. – Eric J. Price May 01 '13 at 15:21

How to find the nearest date?

1 Answers1