CODEWITHSUNDEEP
c#sql-serverlinqentity-framework
Krutal Modi
Krutal Modi

Reputation: 487

How can I get closest datetime from database?

I have following Date Time in my database. If my current system Date time is "2012-11-24 03:50:00.000", then there should be output as 3rd record. How can I get it by linq query? 

1   2012-11-24 01:00:00.000 35466   True    False
2   2012-11-24 01:00:00.000 35466   True    False
3   2012-11-24 01:15:00.000 35466   True    False
4   2012-11-23 01:10:00.000 65456   True    False

Upvotes: 2

Views: 2966

Answers (4)

RePierre
RePierre

Reputation: 9566

The following approach should help you (it's not the optimal approach but it should do the work):

First, select the closest date from the available dates by substracting each date from the value of DateTime.Now and order the results, then select the first item:

var itemWithClosestDate = dbContext.Table
    .OrderBy(x => Math.Abs((DateTime.Now - x.Date).TotalMilliseconds))
    .First();

Upvotes: 0

SAJ14SAJ
SAJ14SAJ

Reputation: 1708

If you don't absolutely have to do the query server-side, then the following pseudocode method will work:

  foreach value in the data set
        let distance = absolute value of ( data point - target data value )

  sort by distance, ascending

  choose first

In C#, you can calculate time intervals easily with the DateTime.Substract method, and take the absolute value of the returned TimeSpan object's Days property, for example.

I am not too expert with SQL date manipulation, but I believe you can do the same sort of thing server side with something similar to:

  select * from table, abs(Datediff(datecolumn.table, sysdatetime)) as distance
  order by distance

and then use the first value.

Sorry, I just noticed you want it in LINQ format, which lets you combine server side and client side work--something similar to this:

(from q in datacontext.Table
let distance = Math.Abs(q.Date.Subtract(DateTime.Today).Ticks)
orderby distance
select q).First();

Upvotes: 3

sayannayas
sayannayas

Reputation: 774

select * from tbl where d=(select max(d) from tbl where d<sysdate())

SQL fiddle-http://sqlfiddle.com/#!2/d577c/3

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

If you want all tied results, an SQL query would be like this. Not sure how this can be translated to LinQ:

SELECT *
FROM tableX
WHERE DateTimeColumn IN
      ( SELECT TOP 1 d
            WITH TIES 
        FROM
          ( SELECT MIN(DateTimeColumn) AS d
            FROM tableX
            WHERE DateTimeColumn >= GETDATE()
          UNION ALL
            SELECT MAX(DateTimeColumn) 
            FROM tableX
            WHERE DateTimeColumn <= GETDATE()
          ) AS tmp
        ORDER BY ABS(DATEDIFF(millisecond, d, GETDATE()))
      ) ;

Upvotes: 0

Related Questions