SQL Date Help Needed

Question

I have the following query

-- DELETE Current month and Month - 1 records from MonthlyTbl
 DELETE FROM dbo.tbl_SDW_MONTHLY_AS
 WHERE [Month Number] > MONTH(GETDATE()) - 2
 AND [Year] = YEAR(GETDATE())

The query does not appear to be deleting the records from December 2013, can you help me fix this?

Answer 1

You can do it this way.

declare @dt datetime 
set @dt = getdate() 

DELETE FROM dbo.tbl_SDW_MONTHLY_AS
WHERE 
100 * [Year] + [Month Number] >= 
(
100 * datepart(YEAR, dateadd(DAY, - datepart(DAY, @dt) - 1, @dt)) + 
datepart(MONTH, dateadd(DAY, - datepart(DAY, @dt) - 1, @dt))
)

Answer 2

Don't use MONTH number and YEAR number.

Use the dates instead.

 DELETE FROM dbo.tbl_SDW_MONTHLY_AS
 WHERE [Date] > DATEADD(MONTH,-2,GETDATE())