CODEWITHSUNDEEP
sqlsql-serversql-server-2008
nadG
nadG

Reputation: 167

SQL time query from 6pm at night to 6am

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;

Upvotes: 3

Views: 31946

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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;

Upvotes: 1

Dirk N
Dirk N

Reputation: 717

How about this?

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

where datefunction converts datetime to date

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

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

Upvotes: 9

Related Questions