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;
qq分组简单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 * ('DI',1,'','') where datediff(month,fc_InsertDateTime, getdate()) =0 本⽉
select * ('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,
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论