how to create time restriction

Question

question

garwend asked Nov 13, '09

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-2005 datetime time

10 |1200

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

Answer 1 · 2009-11-13T11:36:53Z

Scott 2 answered Nov 13, '09

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

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

Answer 2 · 2009-11-16T08:39:04Z

Peso answered Nov 16, '09

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

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

Answer 3 · 2009-11-16T12:04:46Z

garwend answered Nov 16, '09

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

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

Answer 4 · 2009-11-16T18:43:27Z

Peso answered Nov 16, '09

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

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

question