sql 按月查询数据 不允许跨年

大概日期框这个样子 查询时 不可跨年 只能按月查在这里插入图片描述

sql代码如下:

DECLARE @startTime datetime=null     --搜索的开始时间
DECLARE @endTime datetime=null      --搜索的结束时间
--以o.FinishTime为基准
--@where为查询拼接
select @endtime=dateadd(day,1,@endtime)
--时间不为空且查询结束时间大于查询开始时间且不允许跨年查询
if @startTime is not null and @endtime is not null and  @endtime>@startTime and (DATEPART(yy,@endtime)-DATEPART(yy,@startTime)=0)
select @where =@where +' and o.FinishTime >='''+cast(@startTime as varchar(30)) +''' and o.FinishTime< '''+cast(@endtime as varchar(30))+''''
else if  @startTime is not null
select @where=@where+' and o.FinishTime>= '''+cast(@startTime as varchar(30))+''' and o.FinishTime< '''+cast(dateadd(year, datediff(year, 0, dateadd(year, 1, @startTime)), -1) as varchar(30))+''''
else if  @endtime is not null
select @where=@where+' and o.FinishTime< '''+cast(@endtime as varchar(30))+''' and o.FinishTime>= '''+cast(dateadd(year, datediff(year, 0, @endtime), 0) as varchar(30))+''''
 --查询时间为空时默认查询本月第一天---最后一天  
else if @startTime is null and @endtime is NULL 
select @where =@where +' and o.FinishTime >='''+cast(dateadd(month, datediff(month, 0, getdate()), 0) as varchar(30)) +''' and o.FinishTime< '''+cast(dateadd(month, datediff(month, 0, dateadd(month, 1, getdate())), -1) as varchar(30))+''''
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页