SQL time query from 6pm at night to 6am

Question

How to count number of records after 6 at night and 6 in the morning?

This works until 12pm.

SELECT distinct count(barcode) c 
FROM table1 
where DAY(timestamp) = DAY(GETDATE())
  AND MONTH(timestamp) = MONTH(GETDATE())
  AND YEAR(timestamp) = YEAR(GETDATE())
  AND datepart(hh,timestamp) >= 18 
  AND datepart(hh,timestamp) >= 6;

You need to be more clear please. Are you looking for "From 6 PM today to 6 AM tomorrow", or "From 6 PM yesterday to 6 AM today", or "Before 6 AM or after 6 PM today"? — Aaron Bertrand, Mar 19 '13 at 17:13
Also please don't use `timestamp` as a column name. Not only is it completely vague (timestamp of *what*?), it is also a reserved word. — Aaron Bertrand, Mar 19 '13 at 17:17

score 9 · Accepted Answer · answered Mar 19 '13 at 17:04

I think this may be all that you're looking for. It removes any seconds from the GETDATE() call and adds the appropriate amount of hours to it.

SELECT COUNT(barcode)
FROM table1
WHERE timestamp >= DATEADD(HOUR,18,CONVERT(VARCHAR(10), GETDATE(),110))
  AND timestamp <= DATEADD(HOUR,6,CONVERT(VARCHAR(10), GETDATE()+1,110))

SQL Fiddle Demo

Gordon Linoff · Answer 2 · 2013-03-19T18:03:41.060

1

If you are trying to count things by day, but to have the day start at 6 p.m. rather than midnight, just add an offset to the time:

select cast(timestamp + 0.25 as date) as theday, count(barcode)
from table1
group by cast(timestamp + 0.25 as date)
order by theday desc;

If you wanted to do the count for 6p.m. - 6a.m. for multiple days:

select cast(timestamp + 0.25 as date) as theday, count(barcode)
from table1
where datepart(hh, timestamp) in (18, 19, 20, 21, 22, 23, 0, 1, 2, 3, 4, 5)
group by cast(timestamp + 0.25 as date)
order by theday desc;

For the most recent day, you could do:

select top 1 cast(timestamp + 0.25 as date) as theday, count(barcode)
from table1
where datepart(hh, timestamp) in (18, 19, 20, 21, 22, 23, 0, 1, 2, 3, 4, 5)
group by cast(timestamp + 0.25 as date)
order by theday desc;

edited Mar 19 '13 at 18:03

answered Mar 19 '13 at 17:49

Gordon Linoff

1,242,037
58
646
786

1

https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx – Aaron Bertrand Mar 19 '13 at 17:55
1

@AaronBertrand . . . I appreciate that. It will take me a long time to get off the habit, though. – Gordon Linoff Mar 19 '13 at 17:57
Cool, while you're taking suggestions. :-) https://sqlblog.org/blogs/aaron_bertrand/archive/2012/12/21/16567.aspx – Aaron Bertrand Mar 19 '13 at 17:59
@AaronBertrand . . . Sorry, you'll never get me to start lines with a semicolon or comma. I'm a human being who learned human languages. Punctuation doesn't start a line. – Gordon Linoff Mar 19 '13 at 18:00
That wasn't what I was suggesting at all. I was suggesting that statements should be ***terminated*** with a semi-colon. Sentences *end* with punctation, right – Aaron Bertrand Mar 19 '13 at 18:02

score 0 · Answer 3 · answered Mar 19 '13 at 17:11

0

How about this?

where datefunction(timestamp)>=getdate()-1 and (datepart(hh,timestamp)>=18 or datepart(hh,timestamp)<=6)

where datefunction converts datetime to date

answered Mar 19 '13 at 17:11

Dirk N

717
3
9
23

1

By "where datefunction converts datetime to date" did you mean `CONVERT(DATE, [timestamp])`? Also your `OR` clause isn't correctly formed - without proper parentheses won't this include ALL rows before 6 AM for ALL dates? – Aaron Bertrand Mar 19 '13 at 17:16

SQL time query from 6pm at night to 6am

3 Answers3