Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stuck with a getdate time range? Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 4:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:23 AM
Points: 3, Visits: 63
Hi All,

Sorry if this has been asked somewhere all ready but I've struggled to find the answer!

I have a date time field which is a date time stamp on activity. i need to have a query that only brings back information for data that's between today's date and events between 08:00 and 10:00, but i need the date to be dynamic, so that it always returns today's date with that between those times.

My brains says it should be straight forward but i cant suss it.

Thanks
Post #1447945
Posted Tuesday, April 30, 2013 4:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 6,750, Visits: 13,897
Can you work it out from this:

SELECT 
d.Today,
StartTime = DATEADD(hour,8,d.Today),
EndTime = DATEADD(hour,10,d.Today)
FROM (SELECT Today = CAST(CAST(GETDATE() AS DATE) AS DATETIME)) d



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1447949
Posted Tuesday, April 30, 2013 4:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
Here is one way of doing it:


select Col1,Col2
from MyTable
where DateCol > dateadd(hh,8,(dateadd(dd,datediff(dd,'20100101',getdate()),'20100101')))
and DateCol < dateadd(hh,10,(dateadd(dd,datediff(dd,'20100101',getdate()),'20100101')))

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1447950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse