question

garwend avatar image
garwend asked

how to create time restriction

I'm trying to create a daily report and I need some kind of date control. I want it to show only yesterday 11am to today 11am, so between those two times only. Thanks because I'm quite stuck.

                      
SELECT *                    
                    
       DATEADD(d,DATEDIFF(d,0,GETDATE()), 11:00:00.000'),                    
       DATEADD(d,DATEDIFF(d,0,GETDATE()), 11:00:00.000')-1                    
FROM Table1                    
WHERE name = "John"                    
ORDER BY *                    
go                    
sql-server-2005datetimetime
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scott 2 avatar image
Scott 2 answered

If I understand the question, you are almost there. You are trying to query data in a table between 11am yesterday and 11am today, correct? Then, just move your "Date" columns to the where clause:

SELECT *

DATEADD(d,DATEDIFF(d,0,GETDATE()), 11:00:00.000'),
DATEADD(d,DATEDIFF(d,0,GETDATE()), 11:00:00.000')-1 FROM Table1

WHERE name = "John" AND datefield between DATEADD(d,DATEDIFF(d,0,GETDATE()), 11:00:00.000') and
DATEADD(d,DATEDIFF(d,0,GETDATE()), 11:00:00.000')-1 ORDER BY *

go

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

The suggestion above will NOT work. BETWEEN operator need the two parameter in ASCENDING order, not descending order. Replace the parameters and you're set.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

garwend avatar image
garwend answered

Peso, I realized that it didn't work. Can you fix what is written above because I don't completely understand what you are saying. I'm pretty new to sql. also at the top of the query, I added: DECLARE @starttime as datetime SET @starttime

Does that make it better?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

This is the proper way to write your query. It allows a present index to be used.

SELECT  *            
FROM    Table1            
WHERE   Col1 >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), '11:00')            
    AND Col < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '11:00')            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.