很简单吧: select * from MyTable where convert(varchar(6),addtime,112)=convert(varchar(6),dateadd(month,-1,getdate()),112)
select * from MyTable where year(getdate())=year([addtime]) and month(getdate())=month(dateadd(month,-1,[addtime]))
select * from 你的表名 where month(addtime) =month(getdate()) -1 --month函数是通过时间获得月份, --getdate() 是取当前时间,你可以在存储过程中传入你自己的时间参数或指定时间 --当前六月你-1个月就是5月了 可以实现了否?
写错了,刚才是楼上的楼上select * from 你的表名 where month(addtime) =month(getdate()) -1 —————————————————————————————————— 更是离谱,2003,2002年5月份的数据都出来了
SELECT addtime BETWEEN DATEADD(M,-1, CONVERT(NVARCHAR(10),CAST(YEAR(GETDATE()) AS NVARCHAR(4))+'.'+ CAST(MONTH(GETDATE()) AS NVARCHAR(2))+'.01',102) ) AND CONVERT(NVARCHAR(10) , CASE WHEN MONTH(GETDATE())=12 THEN DATEADD(D,-1,DATEADD(M,-1, CAST(YEAR(GETDATE())+1 AS NVARCHAR(4))+'.01.01') ) ELSE DATEADD(D,-1,DATEADD(M,-1,CAST(YEAR(GETDATE()) AS NVARCHAR(4))+'.'+ CAST(MONTH(GETDATE())+1 AS NVARCHAR(4))+'.01')) END ,102)
sorry,没说清楚,你限定下年限就ok了,~你限制下年限 select * from 你的表名 where month(addtime) = month(getdate()) -1 and year(addtime) ='2007' 后面的年份你自己要变化的话写个变量就好了
不死心.select * from MyTable where year(getdate())=year(dateadd(month,1,[addtime])) and month(getdate())=month(dateadd(month,1,[addtime]))把[addtime]加一个月与当前时间比.
select *,year(addtime) as yr,month(addtime) as mt into #t1 from MyTable select @year=year(dateadd(month,-1,getdate())),@month=month(dateadd(month,-1,getdate())),@)select * from #t1 yr=@year and mt=@month
select * from MyTable
where convert(varchar(6),addtime,112)=convert(varchar(6),dateadd(month,-1,getdate()),112)
where year(getdate())=year([addtime]) and month(getdate())=month(dateadd(month,-1,[addtime]))
--month函数是通过时间获得月份,
--getdate() 是取当前时间,你可以在存储过程中传入你自己的时间参数或指定时间
--当前六月你-1个月就是5月了
可以实现了否?
——————————————————————————————————
更是离谱,2003,2002年5月份的数据都出来了
DATEADD(M,-1, CONVERT(NVARCHAR(10),CAST(YEAR(GETDATE()) AS NVARCHAR(4))+'.'+ CAST(MONTH(GETDATE()) AS NVARCHAR(2))+'.01',102) ) AND
CONVERT(NVARCHAR(10) ,
CASE WHEN
MONTH(GETDATE())=12
THEN
DATEADD(D,-1,DATEADD(M,-1, CAST(YEAR(GETDATE())+1 AS NVARCHAR(4))+'.01.01') )
ELSE
DATEADD(D,-1,DATEADD(M,-1,CAST(YEAR(GETDATE()) AS NVARCHAR(4))+'.'+ CAST(MONTH(GETDATE())+1 AS NVARCHAR(4))+'.01')) END
,102)
select * from 你的表名 where month(addtime) =
month(getdate()) -1 and year(addtime) ='2007'
后面的年份你自己要变化的话写个变量就好了
where year(getdate())=year(dateadd(month,1,[addtime])) and month(getdate())=month(dateadd(month,1,[addtime]))把[addtime]加一个月与当前时间比.
select @year=year(dateadd(month,-1,getdate())),@month=month(dateadd(month,-1,getdate())),@)select * from #t1 yr=@year and mt=@month