A simple Select Statement

[Lftsk]

Hello, I have a simple problem that I can't seem to figure out.

I have a table (tblTrans) that has records from the past 3 years. Each record is dated (TransDate). I need a Select Statement that will give me all of the records for the month prior to the current month. This is easy enough except when the prior month is December and the year is the previous year. For example;

SELECT t.*
FROM Transdate t
WHERE MONTH(TransDate) = MONTH(GETDATE() - DATEPART(d, GETDATE()))
AND YEAR(TransDate) = YEAR(GETDATE())

This statement works fine if the current Month is February thru December. But if the current month is January then I need to switch the year back to the previous year to access those records. I'm sure I need to modify the "Year" section in the WHERE clause. I just can't figure it out. This must be done in the select clause. I cannot do it in a stored procedure or with multiple SQL statements.

Thank you.

 

[cmmrfrds]

Could use a case statement.

WHERE TransDate >=
CASE when MONTH(GETDATE()) = 1 then '12/01' + Year(getdate()-1) END
Else MONTH(GETDATE()) + '/01/' + Year(getdate()
AND
TransDate <
CASE when MONTH(GETDATE()) = 12 then '01/01/' + Year(getdate()+1) END
Else MONTH(GETDATE()+1) = '/01/' + Year(getdate()

 

[maswien]

You don't need worry about January, function dateadd will handle that without problem:

SELECT t.* FROM Transdate t
WHERE
month(TransDate) = datepart(m, dateadd(mm, -1, getdate()))