sql按月查询

//按月查询

select * from tb where convert(varchar(7),date,120) = '2011-05'

select * from tb where convert(varchar(10),date,120) = '2011-05-01'

 

//按小时分组，求得每个分组后的个数

SELECT datepart(hour,a.VelPassTime),count(*) num from VelOriginalData a
where a.SensorCommonId = 'f7fb8929-1e58-42c1-9d13-052ddfc82136' and a.VelPassTime >= '2020-04-13 00:00:00' and a.VelPassTime <= '2020-04-14 00:00:00'
and a.VelLoad >= 0 and a.VelLoad <= 10000
group by datepart(hour,a.VelPassTime)