if month(getdate())>29 begin --这个月倒数第4天 select dateadd(dd,-4,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01') end else begin --上个月倒数第3天 select dateadd(dd,-3,convert(varchar(8),getdate(),120)+'01') end
declare @date varchar(10),@f_date varchar(10),@e_date varchar(10) set @date=convert(varchar(10),GETDATE(),23) set @f_date=convert(varchar(4),year(@date))+'-'+cast(MONTH(@date) as varchar(2))+'-1' set @e_date=CONVERT(varchar(4),year(@date))+'-'+cast((MONTH(@date)+1) as varchar(2))+'-1' select DATEADD(day,-3,@f_date) select DATEADD(day,-4,@e_date)
/**-----------------------
2010-07-29 00:00:00.000(1 行受影响)
**/select dateadd(dd,-4,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01')
/**-----------------------
2010-08-28 00:00:00.000(1 行受影响)
**/
cast(convert(varchar(7),getdate()+1,120)+'-01' as datetime) -4
----------------------- -----------------------
2010-07-29 00:00:00.000 2010-07-28 00:00:00.000
select dateadd(day,-3,dateadd(month,DATEDIFF(MONTH,0,GETDATE()),0)) 上个月倒数第3天
,dateadd(day,-4,dateadd(month,DATEDIFF(MONTH,0,GETDATE())+1,0)) 本月倒数第4天
上个月倒数第3天 本月倒数第4天
----------------------- -----------------------
2010-07-29 00:00:00.000 2010-08-28 00:00:00.000(1 行受影响)
DATEADD(DAY, 1 - DAY(DATEADD(MONTH,1,GETDATE())) - 4,DATEADD(MONTH,1,GETDATE()))
begin
--这个月倒数第4天
select dateadd(dd,-4,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01')
end
else
begin
--上个月倒数第3天
select dateadd(dd,-3,convert(varchar(8),getdate(),120)+'01')
end
declare @date varchar(10),@f_date varchar(10),@e_date varchar(10)
set @date=convert(varchar(10),GETDATE(),23)
set @f_date=convert(varchar(4),year(@date))+'-'+cast(MONTH(@date) as varchar(2))+'-1'
set @e_date=CONVERT(varchar(4),year(@date))+'-'+cast((MONTH(@date)+1) as varchar(2))+'-1'
select DATEADD(day,-3,@f_date)
select DATEADD(day,-4,@e_date)