Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

MS SQL Server select specific time range - 1 day

My select below gets the correct data

However I would like to automate the actual between dates
It will always be those times - 1 day

Select i.*
From @tblTemp i
Where  DATEADD(HOUR,-4,i.DateAdded) BETWEEN '2018-09-27 11:29:00.000' AND '2018-09-27 17:31:00.00'
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
First of all: such expressions should be avoided, cause they can be nonsargeble. Move the calculation to the constant side of the expression.

hmm, when I understand you correctly, e.g.

FROM    @tblTemp i
WHERE   i.DateAdded
        BETWEEN DATEADD(DAY, DATEDIFF(DAY, '20180101', GETDATE()) - 1, '20180101 15:29:00.000') 
            AND DATEADD(DAY, DATEDIFF(DAY, '20180101', GETDATE()) - 1, '20180101 21:31:00.00');

Open in new window

Avatar of Larry Brister


Perfect... thanks Scott.
I know you have your answer, so this is possibly nitpicking just a little bit (and might not be an issue for you)

But what if something happens at 21:31:15.00 ?

Since your times are "fixed" then I would suggest a small improvement to your query :
FROM    @tblTemp i
WHERE   i.DateAdded >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '15:29') 
AND     i.DateAdded < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '21:32')

Open in new window

With datetime and datetime2 (or times), because of the level of precision (thousands of a second or more), it is more robust to use greater than or equal to start (date) time, but less than +1 of whatever level of precision you need (in this case a minute).

If your datatype is DATE then no problems using between....