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

Question

I am trying to pull all data for the last 24 hours but starting from the current time. If the current date-time is 5/3 and the time is 11:30 then i want to pull the last 24 hours from 11:30. The data type for date field is datetime and it has only the date and time values without the seconds. Here is my current query

select Name, Location, myDate from myTable where myDate>= getdate()-24

the query above is giving me everything but i only want from the current time. this is how myDate look like in the table

 2015-03-05 10:30:00.000
2015-03-05 11:00:00.000
2015-03-05 11:30:00.000
2015-03-05 12:00:00.000
2015-03-05 12:30:00.000
2015-03-05 13:00:00.000
2015-03-05 13:30:00.000
2015-03-05 14:00:00.000
2015-03-05 14:30:00.000

Solution 1

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

Solution 2

I believe the issue is with:

select Name, Location, myDate from myTable where myDate>= getdate()-24

The -24 as this would be -24 days

try:

select Name, Location, myDate from myTable where myDate>= getdate()-1

An alternative would be to use the date add function:

http://www.w3schools.com/sql/func_dateadd.asp

DATEADD(datepart,number,date)

In your situation you could:

    select Name, Location, myDate from myTable where myDate>= DATEPART (dd, -1, GETDATE())

Where we are adding negative one dd (days)