求sql 日期相差指定月数的数据

[hfyanzi]

表 A

id statedate info
1 2016-11-01 x
1 2016-11-02 u
1 2016-11-03 z
1 2016-11-04 y

今天时期 2016-09-02

想得到距今日刚好2个月的数据

1 2016-11-02 u

[Mr_Nice]

--> 测试数据: [TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA] (id int,statedate datetime,info varchar(1))
insert into [TA]
select 1,'2016-11-01','x' union all
select 1,'2016-11-02','u' union all
select 1,'2016-11-03','z' union all
select 1,'2016-11-04','y'

select * from [TA]



SELECT  *
FROM    TA
WHERE   DATEDIFF(mm, GETDATE(), statedate) = 2
        AND DATEPART(dd, statedate) = DATEPART(dd, GETDATE());

/*
id	statedate	info
1	2016-11-02 00:00:00.000	u*/

[Ginnnnnnnn]

--如果字段格式是date 可以用这种方法 select * from table where startdate = dateadd(mm,2,getdate()) --如果是datetime select * from table where startdate >= convert(varchar(10),dateadd(mm,2,getdate()),120) and startdate < convert(varchar(10),dateadd(mm,2,getdate())+1,120)