How to retrieve records for last 30 minutes in MS SQL?

SqlSql ServerTsql

Sql Problem Overview

I want to retrieve records for last 30 minutes in a table. How to do that? Below is my query..

select * from
[Janus999DB].[dbo].[tblCustomerPlay]
where DatePlayed < CURRENT_TIMESTAMP
and DatePlayed >
(CURRENT_TIMESTAMP-30)

Sql Solutions

Solution 1 - Sql

Change this (CURRENT_TIMESTAMP-30)

To This: DateADD(mi, -30, Current_TimeStamp)

To get the current date use GetDate().

MSDN Link to DateAdd Function
MSDN Link to Get Date Function

Solution 2 - Sql

Have a look at using DATEADD

something like

SELECT DATEADD(minute, -30, GETDATE())

Solution 3 - Sql

Use:

SELECT * 
FROM [Janus999DB].[dbo].[tblCustomerPlay] 
WHERE DatePlayed <  GetDate() 
AND DatePlayed > dateadd(minute, -30, GetDate())

Solution 4 - Sql

DATEADD only returned Function does not exist on MySQL 5.5.53 (I know it's old)

Instead, I found DatePlayed > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 minute) to produce the desired result

Solution 5 - Sql

Remember that CURRENT_TIMESTAMP - (number) works fine, but that you need to understand what number it is looking for - it is floating-point number of days. So CURRENT_TIMESTAMP-1.0 is 1 day ago, CURRENT_TIMESTAMP-0.5 is 1/2 day ago. For 30 minutes, that is 1.0/48.0 (use radix so result is a floating point number) or 0.0208333333333333, so your query will work if re-written as

select * from
[Janus999DB].[dbo].[tblCustomerPlay]
where DatePlayed < CURRENT_TIMESTAMP
and DatePlayed >
CURRENT_TIMESTAMP-1.0/48.0

You could also use 1.0/24.0/2.0 if that looks more like 1/2 hour to you.

Solution 6 - Sql

SQL Server uses Julian dates so your 30 means "30 calendar days". getdate() - 0.02083 means "30 minutes ago".

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Tsql Solutions

Previous Solution:

How to set patience as default git diff algorithm

GitDiff

Next Solution:

PostgreSQL database service

WindowsDatabasePostgresql

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSaravanan I MView Question on Stackoverflow
Solution 1 - Sqlkemiller2002View Answer on Stackoverflow
Solution 2 - SqlAdriaan StanderView Answer on Stackoverflow
Solution 3 - SqlJohn PickupView Answer on Stackoverflow
Solution 4 - SqlJesse NovotnyView Answer on Stackoverflow
Solution 5 - SqlTheRealZingView Answer on Stackoverflow
Solution 6 - SqlSir WobinView Answer on Stackoverflow