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

问题:

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

回答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

回答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)

回答3:

Assuming you're using SQL Server 2012 or above, you could ANSI interval literals instead of dateadd():

select Name, Location, myDate from myTable
 where myDate>= getdate() - INTERVAL '24' HOUR;

If you want to be really ANSI standard, you can use CURRENT_TIMESTAMP instead of getdate() (the two are equivalent).

select Name, Location, myDate from myTable
 where myDate >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR;