February 13, 2012 at 7:46 am
Can anyone tell me how to query data between specific time range.
For example: I would like to get all the rows who's ActionTimeStamp between 7:30am and 8:30am.
Any suggestions would be greatly appreciated.
SELECT * FROM Log
WHERE CONVERT(VARCHAR(10), ActionTimeStamp, 101) = CONVERT(VARCHAR(10), '02/10/2012', 101)
AND ((DATEPART(HH,ActionTimestamp) >= 7 AND DATEPART(MI,ActionTimestamp) >= 30)
AND (DATEPART(HH,ActionTimestamp) <= 8 AND DATEPART(MI,ActionTimestamp) <= 30))
This is the query i use but I do not get all the rows back. I get only the rows that has time stamp of 8:30.
BTW the ActionTimestamp datatype is DATETIME
Note: This time range is selected by the user as the criteria, hence its dynamic. The query I put in here is to try just as an example but eventually it will be dynamic and nothing can be hardcoded. 🙂
February 13, 2012 at 8:03 am
SELECT *
FROM Log
WHERE ActionTimeStamp >= '2012-10-02 07:30:00' AND ActionTimeStamp <= '2012-10-02 08:30:00'
February 13, 2012 at 8:15 am
Thank you Cadavre for your quick response. But this returns zero rows. Also, I cannot hardcode values and it should be dynamic based on the user selected criteria.
February 13, 2012 at 8:22 am
Would need to use parameters and ensure that your passing an ISO date format through to the query, something like the below, so the user can pass in a start and end date.
DELCARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2012-02-10 07:30'
SET @EndDate = '2012-02-10 08:30'
SELECT
*
FROM
Log
WHERE
ActionTimeStamp BETWEEN @StartDate AND @EndDate
February 13, 2012 at 8:37 am
Please will you post some sample data and expected results based on that data? I'm struggling to understand whether you want everything between 7:30 and 8:30 on a particular date, or on any date. If it's the latter, you may consider converting to the time data type.
John
February 13, 2012 at 8:39 am
Vincy-856784 (2/13/2012)
Thank you Cadavre for your quick response. But this returns zero rows. Also, I cannot hardcode values and it should be dynamic based on the user selected criteria.
If my query returned 0, then there is no data matching the criteria that you stated.
The "hardcode" values was for demonstration, you'd use parameters from your app as anthony shows in his post. The point I was trying to get across was that you should use DATETIME data as DATETIME data. By wrapping it in functions as you did, you just confuse the query optimiser and make it take longer (it can't use any indexes that you may have on the DATETIME data in an efficient way).
February 13, 2012 at 9:29 am
Thank you Anthony! Your solution worked perfectly. I had to create the ISO date string and then use between operator to make work perfectly. For some reason the >= and/or <= doesn't work. 🙁
Thank you all for your quick responses.
For those who're looking for the solution like me the exact query is as shown below:
SELECT * FROM Log
WHERE CONVERT(VARCHAR(30),ActionTimestamp, 20) BETWEEN '2012-02-10 08:00' AND '2012-02-10 08:30'
February 13, 2012 at 9:48 am
why are you converting the datetime value to a varchar and then comparing it to a datetime? leave the data types alone and use formatted ISO date formats to ensure data consistancy.
February 13, 2012 at 10:09 am
I tried first but gave me conversion error. But now, it works without converting to string. Thank you Anthony one more time. You saved my day! 🙂
SELECT * FROM Log WHERE ActionTimestamp BETWEEN '2012-02-10 08:00' AND '2012-02-10 08:30'
February 13, 2012 at 7:27 pm
Vincy-856784 (2/13/2012)
I tried first but gave me conversion error. But now, it works without converting to string. Thank you Anthony one more time. You saved my day! 🙂SELECT * FROM Log WHERE ActionTimestamp BETWEEN '2012-02-10 08:00' AND '2012-02-10 08:30'
Heh... not taking a thing away from Anthony but it's amazing that we've come full circle here. 😉
Cadavre (2/13/2012)
SELECT *FROM Log
WHERE ActionTimeStamp >= '2012-10-02 07:30:00' AND ActionTimeStamp <= '2012-10-02 08:30:00'
Unless something is drastically wrong with the datatypes, both should work equally well.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2012 at 8:29 pm
P.S. I won't explain it now because you won't believe me but when you eventually get burned by using BETWEEN with datetime datatypes, remember this post and that I'm telling you it's a terrible habit to get into. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply