CODEWITHSUNDEEP
sql-serverrange
smr5
smr5

Reputation: 2793

SQL Date Range only show data for the last hour

I want to do a mssql date range to return the data for an hour time range. Meaning, I want to return the data for the last hour, but not for the last hour from the current time.

Declare @today datetime
set @today=GETDATE()
select *  from table1  where
datetime>= DATEADD(hh,-2,@Today)

For example, the current time is 11:50:00 a.m. I would like the query to return all the data between 10:00:00 a.m. to 10:59:00 a.m. My variables cannot be static. I would like it dynamic, so no matter what time of the day I run the query it only return the last hour data no matter what time is it now. So it could be any time between 11:00:00 a.m. until 11:59:00 a.m., I still want the result to return data from 10 a.m until 10:59 a.m.

Thank you

Upvotes: 0

Views: 3841

Answers (5)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Here's a slightly simpler way:

DECLARE @d SMALLDATETIME;

SELECT @d = DATEADD(HOUR, DATEDIFF(HOUR, '20000101', GETDATE()) - 1, '20000101');

SELECT @d;

Now you can use @d in your query, e.g.

WHERE col >= @d AND col < DATEADD(HOUR, 1, @d);

This is an open-ended date range. Please don't think about the "end" of the range or consider this a BETWEEN query. BETWEEN 10:00 AND 10:59 is not a very wise approach, because you may miss data from 10:59:00.003 -> 10:59:59.997. Background info on why BETWEEN is evil.

Upvotes: 8

Klark
Klark

Reputation: 8300

declare @today datetime
set @today=GETDATE()
select @today, DATEADD(HOUR, -2, DATEADD(HOUR, DATEDIFF(HOUR, 0, @today), 0)), DATEADD(MINUTE, -1, DATEADD(HOUR, -1, DATEADD(HOUR, DATEDIFF(HOUR, 0, @today), 0)))

result:

2013-11-04 17:42:17.933 2013-11-04 15:00:00.000 2013-11-04 15:59:00.000

Upvotes: 1

jpw
jpw

Reputation: 44921

This should work:

DECLARE @D DATETIME
SET @D = GETDATE()
SELECT @D AS 'Date',
DATEADD(HOUR,-1,DATEADD(MINUTE,-(DATEPART(MINUTE, @D)),DATEADD(SECOND,-(DATEPART(SECOND, @D)),DATEADD(MILLISECOND,-(DATEPART(MILLISECOND, @D)),@D)))) AS 'Range start',
DATEADD(MINUTE,-(DATEPART(MINUTE, @D)),DATEADD(SECOND,-(DATEPART(SECOND, @D)),DATEADD(MILLISECOND,-(DATEPART(MILLISECOND, @D)),@D))) AS 'Range end'

For the date:

2013-11-04 17:35:51.843

This will return a range like:

Start: 2013-11-04 16:00:00.000  
End: 2013-11-04 17:00:00.000

For times between 00:00:00-01:00:00 it will get the range 23:00:00-00:00:00 from the previous day.

Upvotes: 1

Question3CPO
Question3CPO

Reputation: 1202

This returns an hour range, of the previous hour to now; meaning that if it was 11:35AM, it would return 10 AM to 11 AM:

DECLARE @today DATETIME, @hour DATETIME, @hourtwo DATETIME
SET @today = GETDATE()
-- Test other times
--SET @today = '2013-11-04 11:37.22'
SELECT @hour = DATEADD(hh,-2,@today)
SELECT @hourtwo = DATEADD(hh,-1,@today)

SELECT CONVERT(SMALLDATETIME,ROUND(CAST(@hour as float) * (24/1),0)/(24/1)) AS PreviousHourBegin

SELECT CONVERT(SMALLDATETIME,ROUND(CAST(@hourtwo as float) * (24/1),0)/(24/1)) AS PreviousHourEnd

Upvotes: 1

jazzytomato
jazzytomato

Reputation: 7239

Try this one :

declare @lowerRange datetime = 
dateadd(hh,datepart(hour,dateadd(hh,-1,getdate())) , 
        cast(cast(getdate() as date) as smalldatetime) )

declare @upperRange datetime = dateadd(hour,1,@lowerRange)


select * from yourtable where yourdate between @lowerRange and @upperRange

Upvotes: 1

Related Questions