Find record with nearest Date.

I have a table with transactions which include a date column with a British format date. I want to query  this table to return the transaction with the nearest date to my known date where the vehicle is equal to X9ABC and the status is between 1 and 5 and the date is 07/10/2011 14:15:16. The table has 150000 plus records and performance should be considered. I have seen examples using datediff  and Top(1) but don’t know how to build the query for my requirements. Anyone help with this.

Table Structure
Vehicle            Date             Status             Driver
X9ABC            07/10/2011 09:31:22            2            Bob
X9ABC            07/10/2011 11:01:32        5            Bob
X8ABC                              07/10/2011 18:11:09            6            John

Who is Participating?
EyalConnect With a Mentor Commented:
something like that will do

select * from tblname where id in (select top 1 id from tblname order by abs(datediff(ms, [Date], GETDATE()))
Olaf DoschkeSoftware DeveloperCommented:
What type is the date column? If it's a datetime field it is totally unimportant what format you see it in, the datetime binary format is far from the human readable format anyway, and that is what is stored and what you work on, not the human readable format.

Bye, Olaf.
I don't have a working SQL Server to test the syntax on, but it would be something like:
select top 1 *, abs( DATEDIFF(second, '07/10/2011 14:15:16',tablename.Date))  as DD 
from table 
where vehicle="X9ABC" and
status  between 1 and 5
order by DD

Open in new window

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

JoeBo747Author Commented:
Hi Eyal

I have tried your suggestion but it does not return a record.

SELECT     dwJobAuditId, Vehicle
FROM         JobAudit
WHERE     (dwJobAuditId IN
                          (SELECT TOP (1) dwJobAuditId
                            FROM  JobAudit AS JobAudit_1
                            ORDER BY ABS(DATEDIFF(s, dtDate, CONVERT(datetime, '07/10/2011 14:15:16', 103))))) AND (Vehicle = '[X9ABC]')
insure that the inner query returns record
JoeBo747Author Commented:
Hi Eyal

That’s where the issue was, if I write the query as:
SELECT     TOP (1) dwJobAuditId, Registration, dtDate
FROM         JobAudit AS JobAudit_1
WHERE     (Vehicle= ' X9ABC')
ORDER BY ABS(DATEDIFF(s, dtDate, CONVERT(datetime, '07/10/2011 14:15:16', 103)))

The query then returns the record I expect without resorting to the subquery.  Would you use this method ?
it's even better then my version :) use that
JoeBo747Author Commented:
Thanks for both of your comments. But you provided the solution so the points are yours.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.