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'
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
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.

SELECT  i.*
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

ASKER

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 :
SELECT  i.*
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....