1

How could i select the latest records by datetime of an SQL Server?

Here is the pseudo-code...

SELECT Records 
  FROM MyTable 
 WHERE current time >= (CurrentTime - 2 minutes)

Supposing the current Time is 10:25:39 pm

26/10/2009 10:25:39 pm
26/10/2009 10:25:00 pm
26/10/2009 10:24:53 pm
26/10/2009 10:24:19 pm
26/10/2009 10:23:58 pm
26/10/2009 10:14:56 pm
26/10/2009 10:12:56 pm

the SQL query should return these records...

26/10/2009 10:25:39 pm
26/10/2009 10:25:00 pm
26/10/2009 10:24:53 pm
26/10/2009 10:24:19 pm
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
OrElse
  • 9,709
  • 39
  • 140
  • 253

2 Answers2

7

Real code:

SELECT * FROM MyTable WHERE currentTime >= DATEADD(n, -2,  GETDATE())
ORDER BY currentTime DESC
richardtallent
  • 34,724
  • 14
  • 83
  • 123
  • +1 for not putting the DateAdd function on the column! I always love queries that are mindful of potential index use (and I see this 'mess up' happen more than I care for). – KSimons Oct 26 '09 at 20:50
3

Use:

WHERE t.currenttime BETWEEN DATEADD(mi, -2, GETDATE()) AND GETDATE()
ORDER BY t.currenttime DESC

References:

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502