score:8

Accepted answer

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.

Read More

score:1

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

score:1

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

score:1

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.

score:1

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

More questions

More questions with similar tag