Noob - How to group results by just the DATE of Alert using a Timestamp, not TIME?

Cbro

The current Query looks like this!

SELECT [data].[AlertObjects].[Node].[DisplayName] AS [DisplayName],[data].[InstanceSiteId] AS [InstanceSiteId],CAST([data].[timestamp] AS DATE) AS [AlertDate]
FROM orion.alerthistory AS data
WHERE
(((([data].[AlertObjects].[AlertObjectID]) IS NOT NULL) AND (([data].[AlertObjects].[InstanceSiteId]) IS NOT NULL) AND (([data].[AlertObjects].[Node].[NodeID]) IS NOT NULL) AND (([data].[AlertObjects].[Node].[InstanceSiteId]) IS NOT NULL)) AND ((([data].[AlertObjects].[AlertID]) = (270)) AND (([data].[EventType]) = (1)) AND (([data].[AlertObjects].[EntityCaption]) NOT LIKE '%'+('Virtual Memory') + '%')))
WITH NOLOCK

When I try and group by TimeStamp (Alert History), I struggle to get it to only group by the DATE the Alerts Triggered. It will seperate them by TIME too.

Any help would be appreciated!

adam.beedell

Search "Window Functions" for the proper into to what you're trying to do

Answering directly you can output something like:

substring(tostring(DATETRUNC('day',GETUTCDATE())),1,11) as [day]

Substituting GETUTCDATE() for whatever timestamp, or using any other method doesnt matter really.

You'd GROUP BY a less edited function like just DATETRUNC or DAY or something