How to Select Records from Last 24 Hours Using SQL

How to select records from last 24 hours using SQL?

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

How to get last 24 hours from current time-stamp?

To be more explicit with your intentions, you may want to write your query like so:

 select Name, Location, myDate from myTable where myDate>= DATEADD(hh, -24, GETDATE())

SQL Server DATEADD

SQL Query: Where datetime = past 24 hours, how?

You want to use the dateadd function to add -24 hours to now (getdate()) and then compare that to your eventdatetime column e.g.

select eventdatetime, eventid, sCode, sNarrative
from [Blue Prism].dbo.BPAAuditEvents
where eventdatetime > dateadd(hour, -24, getdate());

Getting last 24 hours from current time in sql

Use BETWEEN, ie

select Name, location, myDate from myTable where myDate between DATEADD(hh, -24, GETDATE()) and GETDATE()

This myDate >= DATEADD(hh, -24, GETDATE()) gets you all records where myDate is greater than 24 hours ago, including records that have future dates(if they are correct to have future dates is another story...)

Get last 24 hours from selected date using SQL

select ID, dateadd(hh, -24, FullDatetime) from myTable  /* or (dd, -1, ...) */
where selectedDate = 0900 /* did you mean '0900'? */
    and DT = CONVERT(date, getdate())

I suspect you could also avoid the query entirely by calculating it directly:

select dateadd(hh, -15, {fn current_date()})

Return records with counts for the last 24 hours

Try this, it will also count the hours without data:

DECLARE @t table(ID int, Date datetime)
INSERT @t values
(1,'2015-01-19 10:29:00.000'), (2,'2015-01-19 11:29:00.000'), 
(3,'2015-01-19 11:29:00.000'), (4,'2015-01-19 11:29:00.000'), 
(5,'2015-01-19 12:29:00.000'), (6,'2015-01-19 12:29:00.000'), 
(7,'2015-01-19 12:29:00.000'), (8,'2015-01-19 12:29:00.000'), 
(9,'2015-01-17 13:29:00.000'), (10,'2015-01-17 13:29:00.000'), 
(11,'2015-01-17 13:29:00.000'),(12,'2015-01-17 13:29:00.000'), 
(13,'2015-01-17 13:29:00.000'),(14,'2015-01-17 13:29:00.000'), 
(15,'2015-01-17 14:29:00.000'),(17,'2015-01-17 15:29:00.000'), 
(18,'2015-01-17 15:29:00.000'),(19,'2015-01-17 16:29:00.000'), 
(20,'2015-01-17 16:29:00.000'),(21,'2015-01-15 16:29:00.000'), 
(22,'2015-01-15 17:29:00.000'),(23,'2015-01-15 18:29:00.000'), 
(24,'2015-01-15 18:29:00.000'),(25,'2015-01-15 18:29:00.000'), 
(26,'2015-01-15 18:29:00.000'),(27,'2015-01-15 18:29:00.000'), 
(28,'2015-01-15 18:29:00.000'),(29,'2015-01-15 19:29:00.000'), 
(30,'2015-01-10 20:29:00.000')

DECLARE @yourdate datetime = '2015-01-19T12:30:00.000'

;WITH CTE AS
(
  SELECT dateadd(hh, datediff(hh, 0, @yourdate), 0) Date
  UNION ALL
  SELECT dateadd(hh, -1, Date)
  FROM CTE 
  WHERE Date + 1 > @yourdate
)
SELECT CTE.Date, count(t.id) count
FROM CTE
LEFT JOIN @t t
ON CTE.Date <= t.Date
and dateadd(hh, 1, CTE.Date) > t.Date
GROUP BY CTE.Date
ORDER BY CTE.Date DESC

Result:

Date                    Count
2015-01-19 12:00:00.000 4
2015-01-19 11:00:00.000 3
2015-01-19 10:00:00.000 1
2015-01-19 09:00:00.000 0
2015-01-19 08:00:00.000 0
.....

select records with date in the last 24 hours

You already have a lower limit on the date, but since your table can have future dates you also need an upper limit. This should work:

SELECT *
FROM my_table
WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND date <= NOW()

---

Related Topics