We help IT Professionals succeed at work.

MS SQL Server select specific time range - 1 day

73 Views
Last Modified: 2018-09-28
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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....