SQL日期处理技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/zhang_bamboo/article/details/5634650

本文介绍了一种使用SQL来处理日期范围的方法，通过设置变量来自动计算统计周期的开始和结束时间，并展示了如何根据当前时间来获取上个月的具体日期，适用于需要定期统计的数据报表场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最笨的方法

declare @TimeEnd varchar(30),
   @TimeStart varchar(30),
   @subs varchar(2)
--计算统计的开始时间和结束时间
set @Time=convert(varchar(30),@Time,120)
--
select @TimeStart=rtrim(ltrim(Remark)) from dbo.BaseInfo where BaseInfoID=139--获取分隔时间
select @TimeEnd=rtrim(ltrim(Remark)) from dbo.BaseInfo where BaseInfoID=140
set @subs=month(@Time)--获取月份
if(len(@subs)=1)
  set @TimeEnd=cast(year(@Time) as varchar(4))+'-0'+@subs+'-'+@TimeEnd
else
  set @TimeEnd=cast(year(@Time) as varchar(4))+'-'+@subs+'-'+@TimeEnd

set @subs=cast(cast(@subs as int)-1 as varchar(2))
if(len(@subs)=1)
set @TimeStart=cast(year(@Time) as varchar(4))+'-0'+@subs+'-'+@TimeStart
else
set @TimeStart=cast(year(@Time) as varchar(4))+'-'+@subs+'-'+@TimeStart
--select @TimeStart,@TimeEnd

简化：

declare
@TimeStart varchar(30),
@TimeEnd varchar(30),
@time varchar(30),
@DetpID varchar(36)
set @DetpID='43b5d11c-e8be-4cfd-ac99-ddff342ca7ad'
set @time='2010-06-02'
select @TimeEnd=rtrim(ltrim(Remark)) from dbo.BaseInfo where BaseInfoID=140
set @TimeEnd=replace(@Time,substring(@Time,9,2),@TimeEnd)
set @TimeStart=Convert(varchar(30),DATEADD(day,1,DATEADD(month, -1, convert(datetime,@TimeEnd,120))),120)
select cast(sum(convert(float,Isnull(Scores,100))/100*convert(float,Remark))/sum(convert(float,Remark)*convert(float,Isnull(Complete,1)))*100 as decimal(9,4)) as source
from View_GetWorkScore
where TimeEnd between @TimeStart and @TimeEnd and DetpID=@DetpID

select DATEADD(month, -1, getdate())--上一个月
select DATEADD(day, -1, getdate()) --前一天
select getdate()