Reputation: 181
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
Reputation: 709
Try this,
SELECT *
FROM Student
WHERE STARTIME >= Dateadd(MINUTE, -5, GETDATE())
Upvotes: 2
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
Reputation: 2111
select * from Student where startTime is not null and startTime > DATEADD(minute, -5, GETUTCDATE())
try this code
Upvotes: 0