sql 时间截取 分组 获得小时数据常用的方法

       switch (buttondate)
                    {
                        case "hours":
                            where += " and DataCreateOn>DATEADD(HOUR,-24,GETDATE()) ";//" and TO_DAYS(now()) - TO_DAYS(DataCreateOn) <= 7 ";
                            break;
                        case "twodays":
                            where += " and DataCreateOn>DATEADD(HOUR,-48,GETDATE()) ";//" and TO_DAYS(now()) - TO_DAYS(DataCreateOn) <= 7 ";
                            break;
                        case "week":
                            where += " and datediff(day,datacreateon,getdate())<=7 ";//" and TO_DAYS(now()) - TO_DAYS(DataCreateOn) <= 7 ";
                            break;
                        case "month":
                            where += "and datediff(day,datacreateon,getdate())<=30 ";
                            break;
                        case "qq":
                            where += " and datediff(qq,DataCreateOn,getdate())=1 ";
                            break;
                        case "year":
                            where += " and  datediff(year,DataCreateOn,getdate()-1)=0 ";
                            break;
                    
                    }

查询中 24小时,两天,一周,一月.季度,年

下边是查询时间 查询字符串只保留到日例如:2020-12-12,与数据库时间字段不统一问题

  where += " and  convert(char(10),DataCreateOn,120) >=  '" + strStartDate + "' and  convert(char(10),DataCreateOn,120)<='" + strEndDate +"' ";

 string columns = "  Position,datepart(YY,DataCreateOn) as year,datepart(mm,DataCreateOn) as month,datepart(dd,DataCreateOn) as day,datepart(hh,DataCreateOn) as hour , avg(Datavalue)as Datavalue ";
                string groupBy = " group by Position,datepart(YY,DataCreateOn),datepart(mm,DataCreateOn),datepart(dd,DataCreateOn), datepart(hh,DataCreateOn) ";
                string orderby = " order by year,month,day,hour ";

上边是 sql 按小时分组之后,为了显示出列字段

 select getdate()

DataCreateOn='2020-12-05 08:34:18.843'

本月上月数据数据

  select * from dbo.xxx('DI',1,'','') where  datediff(month,fc_InsertDateTime, getdate()) =0  本月

  select * from dbo.xxx('DI',1,'','') where  datediff(month,fc_InsertDateTime, getdate()) = 1  上月

四季

当前时间季度 datediff(qq,DataCreateOn,getdate())=0  //冬季 10,11,12  月时间范围内

上一季度datediff(qq,DataCreateOn,getdate())=1 //秋季 7,8 ,9

              datediff(qq,DataCreateOn,getdate())=2 //夏季4,5,6,

              datediff(qq,DataCreateOn,getdate())=3 //春季1,2,3,