Group by dateTime (Set from till)

Question

I am trying to create a group by date function. But i don't want to get results from 00:00 till 23:59, i want to get results from 6:00 till 5:59. Can i change date from till statement? Example

Select * from Sales group by Convert(date,dateOfSell)
//set statement from 6:00 till next day 5:59

Answer 1

First substract 6 hours from the datetime, and then cast it do a date. Then you can group on this new date.

e.g.

SELECT CAST(DATEADD(hour,-6,dateOfSell) AS DATE),MIN(dateOfSell),MAX(dateOfSell),COUNT(*)
FROM Sales 
GROUP BY CAST(DATEADD(hour,-6,dateOfSell) AS DATE)