MS SQL Server select specific time range - 1 day

Larry Brister
Larry Brister used Ask the Experts™
on
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'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
WHERE i.DateAdded BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '15:29') AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '21:31')

If possible, you should always avoid using functions on columns.  Therefore, rather than subtracting 4 hours from the column, I added 4 hours to the time ranges.
ste5anSenior Developer

Commented:
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 Bristersr. Developer

Author

Commented:
Perfect... thanks Scott.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial