T-SQL – trend per day, per hour in a day

Introduction

the use of the T-SQL date functions : DATEPART
Building a temporary table

SELECT DATEPART(year, '12:10:30.123')
    ,DATEPART(month, '12:10:30.123')
    ,DATEPART(day, '12:10:30.123')
    ,DATEPART(dayofyear, '12:10:30.123')
    ,DATEPART(weekday, '12:10:30.123');

Day trend

select 
datepart(dy, DateSurvey) AS DayOfYear,DateSurvey, XID 
into 
#allLogins2 
from 
Survey with (nolock) 
Where DateSurvey IS NOT NULL
AND VIP IS NULL

select 
DayOfYear, count(*) 
from 
#allLogins2
Group By DayOfYear
order By DayOfYear

Hour of Day trend

select 
datepart(hour, EventDate) AS hourofday, LoginNumber 
into 
#allLogins8 
from 
LogWebEvent with (nolock) 
where 
EventType = 50 
AND EventDate <= '20110605' AND EventDate > '20110604' 

select 
hourofday, count(*) 
from 
#allLogins8
Group By hourofday
order By hourofday

Reference

[1] http://msdn.microsoft.com/fr-fr/library/ms174420.aspx