Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi, I would like to ask your help on how could i get a 2 hour data from the database, on specific hour. Once the query run for example by 3pm, it will get the 1:00-3:00 pm data. Hopefully you could help me modify the below query.

What I have tried:

SELECT *
FROM
dbo.Products where
[LoadingDateTime] >= DATEADD(Hour, -2, GETDATE())
group by [LoadingDateTime]
order by [LoadingDateTime] asc
Posted
Updated 23-Mar-20 9:24am
Comments
MadMyche 23-Mar-20 8:41am
   
So basically if you run it at 3:01 pm you want 1:00pm thru 3:00pm; kinda like you want to round the time down to the "solid" hour?
Rate this:
Please Sign up or sign in to vote.

Solution 1

You just have to specify the upper bound of the range (you only specified the lower bound):
-- Version 1
SELECT
 *
FROM
 dbo.Products
WHERE
 [LoadingDateTime] >= DATEADD(Hour, -2, GETDATE())
 AND [LoadingDateTime] <= GETDATE()
ORDER BY
 [LoadingDateTime] ASC

-- VERSION 2
SELECT
 *
FROM
 dbo.Products
WHERE
 [LoadingDateTime] BETWEEN DATEADD(Hour, -2, GETDATE()) AND GETDATE()
ORDER BY
 [LoadingDateTime] ASC
   
Comments
sophia kylie taylor 23-Mar-20 5:16am
   
is there anyway i could get the data within the specific hour? When I tried to run the script, the earlier minutes wasn't capture since it depend on the date time i run the script. really appreciate your help.
phil.o 23-Mar-20 6:05am
   
Sorry, I don't understand 'the earlier minutes wasn't capture'. Can you give an example with concrete data?
RamiroX 23-Mar-20 7:01am
   
What about the use of DATEDIFF function like DATEDIFF(hour, LoadingDateTime, GETDATE()) = 2 ?
Rate this:
Please Sign up or sign in to vote.

Solution 2

Sophia said:
the earlier minutes wasn't capture since it depend on the date time i run the script.
I think by this you mean that if the current time is 11:14 Solution 1 will literally return everything within the last 2 hours i.e. from 9:14 to 11:14 but what you actually want is everything from 09:00 to 11:00.

If so then @RamiroX is close. Try
SELECT [LoadingDateTime]
FROM
@Products where
DATEDIFF(hour, [LoadingDateTime], getdate()) <= 2
group by [LoadingDateTime]
order by [LoadingDateTime] asc


One other point:
- Don't use Select * if using Group By - list the columns explicitly. To be honest, this is true in general for good coding practice.
   
Comments
Richard Deeming 23-Mar-20 15:20pm
   
That query won't be SARGable. It would probably be better to calculate the start and end times instead. :)
CHill60 24-Mar-20 6:34am
   
Good point, about to 5 your solution!
Rate this:
Please Sign up or sign in to vote.

Solution 3

A slight variation on solution 2 which will let SQL use an index on your date column (if there is one):
DECLARE @Now datetime = GETDATE();
DECLARE @Max datetime = DateAdd(hour, DateDiff(hour, 0, @Now), 0);
DECLARE @Min datetime = DateAdd(hour, -2, @Max);

SELECT [LoadingDateTime]
FROM dbo.Products 
WHERE [LoadingDateTime] >= @Min And [LoadingDateTime] < @Max
GROUP BY [LoadingDateTime]
ORDER BY [LoadingDateTime] ASC;
Non-SARGable Predicates - Brent Ozar Unlimited®[^]
sql server - T-SQL datetime rounded to nearest minute and nearest hours with using functions - Stack Overflow[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100