Avatar of Larry Brister
Larry Brister
Flag 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'
Microsoft SQL Server 2008Microsoft SQL ServerAWS RDS

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ste5an

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

Larry Brister

ASKER
Perfect... thanks Scott.
Mark Wills

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....
Your help has saved me hundreds of hours of internet surfing.
fblack61