x

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

more ▼

asked Nov 13, 2009 at 11:10 AM in Default

avatar image

garwend
4 1 1 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Nov 13, 2009 at 11:36 AM

avatar image

Scott 2
36 2 1

(comments are locked)
10|1200 characters needed characters left

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')            
more ▼

answered Nov 16, 2009 at 06:43 PM

avatar image

Peso
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 16, 2009 at 08:39 AM

avatar image

Peso
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Nov 16, 2009 at 12:04 PM

avatar image

garwend
4 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x81
x36

asked: Nov 13, 2009 at 11:10 AM

Seen: 2829 times

Last Updated: Nov 17, 2009 at 07:59 AM

Copyright 2016 Redgate Software. Privacy Policy