用年月日拼接日期,然后通过日期查询对应数据
DECLARE @today DATETIME, -- 今天
@month_bg DATETIME,
@month_end DATETIME,
@year int,
@month int,
@day int,
set @year=${if(len(年份)=0," DATENAME(year,GETDATE())","'"+年份+"'")}
set @month=${if(len(月份)=0," datename(month,getdate())","'"+月份+"'")}
set @day=datename(day,getdate())
set @today=CONVERT(VARCHAR(10),@year)+'-'+CONVERT(VARCHAR(2),@month)+'-'+CONVERT(VARCHAR(2),@day)
SELECT @month_bg= CONVERT(VARCHAR(8),@today,120)+'01' -- 本月初日期
select @month_end=dateadd(day,-1,convert(varchar(8),dateadd(m,1,@month_bg),120)+'1 00:00:00') -- 本月末日期
用年、季度拼接季度末日期
DECLARE @today DATETIME, -- 今天
@month_bg DATETIME,
@month_end DATETIME,
@to_month VARCHAR(2),
@to_quarter INT, -- 本季度
@to_year varchar(4), -- 本年
@quarter_end datetime -- 季度末日期
SET @to_quarter=${if(len(季度)=0," datename(quarter,getdate())","'"+季度+"'")}
SET @to_year=${if(len(年份)=0," datename(year,getdate())","'"+年份+"'")}
SET @to_month= CASE WHEN @to_quarter='1' THEN '3'
WHEN @to_quarter='2' THEN '6'
WHEN @to_quarter='3' THEN '9'
WHEN @to_quarter='4' THEN '12'
END
set @quarter_end= DATEADD(QUARTER,DATEDIFF(QUARTER,0,CONVERT(DATE,@to_year+'-'+@to_month+'-1',20))+1,-1)