CODEWITHSUNDEEP
sql-serverdatetime
PdeRover
PdeRover

Reputation: 107

T-SQL group by date instead of datetime

select [DateTime (UTC)],  
sum([messages sent external]) as 'messages sent external',
sum([messages sent internal]) as 'messages sent internal',
sum([messages received external]) as 'messages received external',
sum([messages received internal]) as 'messages received internal',
max([Message Latency Internal High]) as 'Message Latency Internal High', 
max([Message Latency Internal Avg]) as 'Message Latency Internal Avg' from dbo.Monthly_MailFlowStats_2014_03 group by [DateTime (UTC)] order by [DateTime (UTC)] asc

The results show all the data in datetime by each hour of the day. Can I trim the datetime to only be date so the results show data for the day and not hour of the day?

This: 
2014-03-01 

instead of:
2014-03-01 00:00:00.000
2014-03-01 01:00:00.000
2014-03-01 02:00:00.000

Upvotes: 1

Views: 152

Answers (3)

Metaphor
Metaphor

Reputation: 6405

select cast([DateTime (UTC)] AS date) AS [Date],  
sum([messages sent external]) as 'messages sent external',
sum([messages sent internal]) as 'messages sent internal',
sum([messages received external]) as 'messages received external',
sum([messages received internal]) as 'messages received internal',
max([Message Latency Internal High]) as 'Message Latency Internal High', 
max([Message Latency Internal Avg]) as 'Message Latency Internal Avg' 
from dbo.Monthly_MailFlowStats_2014_03 
group by cast([DateTime (UTC)] AS date) 
order by [Date] asc

Upvotes: 0

D Stanley
D Stanley

Reputation: 152556

Sure, just trim the time portion off:

select DATEADD(dd, DATEDIFF(dd, 0, [DateTime (UTC)]), 0) [DateTime (UTC)],  
    sum([messages sent external]) as 'messages sent external',
    sum([messages sent internal]) as 'messages sent internal',
    sum([messages received external]) as 'messages received external',
    sum([messages received internal]) as 'messages received internal',
    max([Message Latency Internal High]) as 'Message Latency Internal High', 
    max([Message Latency Internal Avg]) as 'Message Latency Internal Avg' 
from dbo.Monthly_MailFlowStats_2014_03 
group by DATEADD(dd, DATEDIFF(dd, 0, [DateTime (UTC)]), 0) 
order by [DateTime (UTC)] asc

Note that there are several ways to trim off the time portion of a date; this is just one of them

Upvotes: 1

Bryan
Bryan

Reputation: 17693

Assuming you're on SQL Server 2008+:

select
    CAST([DateTime (UTC)] AS DATE) AS 'Date (UTC)',
    sum([messages sent external]) as 'messages sent external',
    sum([messages sent internal]) as 'messages sent internal',
    sum([messages received external]) as 'messages received external',
    sum([messages received internal]) as 'messages received internal',
    max([Message Latency Internal High]) as 'Message Latency Internal High',
    max([Message Latency Internal Avg]) as 'Message Latency Internal Avg' 
from
    dbo.Monthly_MailFlowStats_2014_03 
group by
    CAST([DateTime (UTC)] AS DATE)
order by
    [DateTime (UTC)] asc

Upvotes: 0

Related Questions