dhamo
dhamo

Reputation: 181

How to get last 5 mins record from SQL database table?

How to get last 5 mins records from SQL table.

E.g: I have one table name called Student.

ID Name        StartTime
-- ----------  ----------------------
1  abc         2016-05-18 08:00:29.587 
2  xxx         2016-05-18 08:05:30.287
3  xyz         2016-05-18 08:10:30.287

Consider now the Time is 8:15am. I would like to get last 5mins record from "Student" table.

E.g: I need "xyz" user information.

I tried below queries not working.

select * from Student where startTime is not null and startTime <  GetDate() and startTime > dateadd(minute, -5, GetDate())

But i am getting null values from student table.

How to do it. please help me to solve.

Upvotes: 1

Views: 1757

Answers (3)

PP006
PP006

Reputation: 709

Try this,

SELECT *
FROM   Student 
WHERE  STARTIME >= Dateadd(MINUTE, -5, GETDATE()) 

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

This might be due to the seconds component of the datetime. What you want to do is make sure you remove the seconds from the datetime. Here is how:

SELECT CAST(CONVERT(VARCHAR(16), GETDATE(), 120) + ':00' AS DATETIME)

And then use the result in the comparison, like this:

CREATE TABLE Student(
    ID      INT,
    Name    VARCHAR(3),
    StartTime   DATETIME
);
INSERT INTO Student VALUES
(1, 'abc',  '2016-05-18 08:00:29.587 '),
(2, 'xxx',  '2016-05-18 08:05:30.587 '),
(3, 'xyz',  '2016-05-18 08:10:30.587 ');

DECLARE @currDate DATETIME = '2016-05-18 08:15:59.000'

SELECT @currDate =  CAST(CONVERT(VARCHAR(16), @currDate, 120) + ':00' AS DATETIME)

SELECT  *
FROM Student
WHERE startTime > DATEADD(MINUTE, -5, @currDate)


DROP TABLE Student;

RESULT:

ID          Name StartTime
----------- ---- -----------------------
3           xyz  2016-05-18 08:10:30.587

Upvotes: 2

Chetan Sanghani
Chetan Sanghani

Reputation: 2111

select * from Student where startTime is not null and startTime > DATEADD(minute, -5,  GETUTCDATE())

try this code

Upvotes: 0

Related Questions