Lets say we have some data in a SQL table that represents a series of events with the time that the event occured. The problem is your can't group this data because each Date is exact. You need to round it. Try this.
SELECT
cast((cast(cast (getdate() as float)*288.0 as int)+0.00001) /288.0 as datetime) as TimeToFiveMin,
cast((cast(cast (getdate() as float)*96.0 as int)+0.000003) /96.0 as datetime) as TimeToFifteenMin,
cast((cast(cast (getdate() as float)*24.0 as int)+0.00000075) /24.0 as datetime) as TimeToHour
Note that the small decimal takes care of rounding issues to millisecond accuracy for normal SQL formatting. You may need to tweak this if you use higher accuracy.
Don't forget that in order to group values you need to repeat the value in the select, i.e.
SELECT
cast((cast(cast (DateCreated as float)*288.0 as int)+0.00001) /288.0 as datetime) as TimeToFiveMin,
count(*) as count
FROM dbo.tblMailSendAttempt where DateCreated > getdate() -1
group by cast((cast(cast (DateCreated as float)*288.0 as int)+0.00001) /288.0 as datetime)
order by 1 DESC
Subscribe