how to do an addYears(-2) in T-SQL

I have a variable @.yr which represents the years column in my table, and there is also a Months column, which may or may not have all the months in each year, so I'm trying to get the # of months in a 2 year period - - -

Heres' where I'm stuck:

DECLARE @.yr AS Int

SELECT count(*) AS cnt, month, year
FROM Sales_history
WHERE year BETWEEN @.yr AND @.yr (addyears(- 2))

I put the addyears part because I know you can work with stuff like that in VB.net - - - -but I don't know how to construct this in T-SQL - - help!!

The T-SQL function you're looking for is DATEADD.

http://msdn2.microsoft.com/en-US/library/ms186819.aspx

So, DATEADD(year, -2, @.yr).|||I think I read your post too fast though. DATEADD needs a date for that third parameter. Since @.yr is an int already, why don't you just do your subtraction there. If you need mid year ranges (instead of January to December), you will most likely need to compose a date from your month/year data.|||

As Hous68 said, you can use DATEADD function in T-SQL. But if the year column in the table is INT, why not directly compare it with @.yr:year BETWEEN @.yr-2 AND @.yr ? If theyearcolumn is DATETIME, and since you only what to compare the year part of the datetime to @.yr, you can use YEAR() function to get th year part for comparation: YEAR(year) BETWEEN @.yr-2 AND @.yr .

|||

SELECT count(*) AS cnt, month, year
FROM Sales_history
WHERE year BETWEEN @.yr AND @.yr-2

GROUP BY month,year