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,