Query previous day with GETDATE()-1 and a time period from/to

Question

I want to get total counts from SuperScottTable1 from the previous day and narrow to a time frame (From-To). The below works fine until I add

AND (time > '08:00:00.000' AND time < '22:00:00.000')

It does not error, just returns null.

Is this possible to do?

SELECT 
    SUM(COALESCE(confirmedCount, 0))
FROM 
    SuperScottTable1
WHERE
    superLaneID = '90099' 
    AND time >= GETDATE()-1 
    AND (time > '08:00:00.000' AND time < '22:00:00.000')

Do whatever you need to do to create a start and end datetime, and use those. — Dan Bracuk, Feb 24 '16 at 13:04
Maybe try CAST/CONVERT ? Ref. https://msdn.microsoft.com/en-us/library/ms187928.aspx — Leptonator, Feb 24 '16 at 18:26
The time is the date/time as Gordon provided below, sorry about not defining that. I will try these and see how it goes. Sorry for late reply, I did not receive e-mail that there was any responses, I will check on that. Thx! — Scott_K, Feb 25 '16 at 13:02
Is that column [time] date and time (datetime)? Are you still considering the solutions provided? Suggest you avoid using datepart() if you can, using functions on data in a where clause is a cause of poor query performance. — Paul Maxwell, Mar 01 '16 at 00:03
This is what I ended up using; SELECT SUM(COALESCE(confirmedCount,0)) FROM SorterLaneStatistics WHERE sorterLaneID = '90099' AND time >= DATEADD(hh, -12, GETDATE()) — Scott_K, Mar 02 '16 at 01:14

score 1 · Answer 1 · answered Feb 27 '16 at 04:09

You can do some CAST/CONVERT shenanigans to generate a starting and stoping DATETIME value which you can then compare to your time column. If the time column is indexed then this will allow the server to do a simple range search on the index to find matches.

WHERE
    superLaneID = '90099'
    AND time > CAST(CONVERT(VARCHAR(20), GETDATE()-1, 112) + ' 08:00:00' AS DATETIME)
    AND time < CAST(CONVERT(VARCHAR(20), GETDATE()-1, 112) + ' 22:00:00' AS DATETIME)

Gordon Linoff · Answer 2 · 2016-02-27T02:47:19.537

0

Presumably, time is stored as a datetime. You seem to be using SQL Server, so you can extract the hour and use that:

WHERE superLaneID = '90099' AND
      time >= GETDATE()-1 AND
      datepart(hour, time) >= 8 AND
      datepart(hour, time) < 22

EDIT: To get the "other" hours:

WHERE superLaneID = '90099' AND
      time >= GETDATE()-1 AND
      (datepart(hour, time) < 8 or
       datepart(hour, time) >= 22
      )

edited Feb 27 '16 at 02:47

answered Feb 24 '16 at 13:03

Gordon Linoff

1,242,037
58
646
786

Gordon, this worked well! Thank you!! Also in advance as I am learning this and may have future questions. – Scott_K Feb 25 '16 at 13:11
Gordon, I know that;GETDATE()-1 AND datepart(hour, time) >= 5 AND datepart(hour, time) < 18 will get me 5am to 5pm. Is there anything special to get 5pm to 5am, wrapping around midnight. Will there be two statements needed, 1 for before midnight, and one from 12:00 to 5am? – Scott_K Feb 26 '16 at 12:59
Does this make any sense?? WHERE time >= GETDATE() AND datepart(hour, time) >= 5 AND datepart(hour, time) < 18 – Scott_K Feb 26 '16 at 16:11

score 0 · Answer 3 · edited May 23 '17 at 11:45

GETDATE() in SQL Server returns the system date and time to arrive at the current day (i.e. date at time 00:00:00.0000000) you can use either of these:

cast(getdate() as date) -- available from SQL 2008 onward
dateadd(day, datediff(day,0, getdate() ), 0) -- any SQL Server version

Using these to establish the current date, then add (8-24) hours and or (22-24) hours to establish the boundaries of yesterday e.g.

WHERE superLaneID = '90099'
    AND time >= dateadd(hour,(8-24),cast(getdate() as date))
    AND time < dateadd(hour,(22-24),cast(getdate() as date))

Avoid using non-sargable predicates (such as using a function) that require you to alter each row of data to compare to a fixed value. ref this prior answer

Btw: This method discussed here would work for a time range that spans midnight.

Query previous day with GETDATE()-1 and a time period from/to

3 Answers3