How to find the nearest date?

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.

Answers


Something like this:

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

Should be able to adapt that to your needs


Need Your Help

PHP: Array appears to reinitialize

php arrays oop

I'm trying to make a basic html form that passes information to a php object then adds said object to an array. It works to the point of passing the information from the form, to the object, and a...

SQL Server : Authorize User not in domain

sql-server sql-server-2014-express sql-server-authentication

I have notebook with Windows 8 and SQL Server 2014 Express installed.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.