truncating a summary table with only month and year columns to work with

[bjswift]

I have a data summary table, which has 2 columns I get to work with, year and month. Now, I need to truncate the table in a stored procedure for all of the data older than a certain date (lets just say 5 months from today's date).

I would like it to be a simple delete statement, with a where clause, but I can't say delete from TABLE where year <= datepart(yy,getdate()) and month <= datepart(mm, getdate())

because if the target delete tate was 4/2005 then everything will be deleted before 4/2005 except for 5/2004 6/2004 7/2004 8/2004 9/2004 10/2004 11/2004 12/2004...

Now, I thought about combining the date values, forming an integer such as 200504, which should work, but it is a lot of concatination... Guess getting the job done sometimes is getting it done cleverly.

Any suggestions?

[ITGURU]

Dear bjswift,

The best way to implement this functionality which i will follow is as follows:
1. Create a function which return current year + month in numbers. e.g.

CREATE FUNCTION dbo.GetCurrentYearMonth()
RETURNS INT
AS

RETURN (CONVERT(INT, CONVERT(VARCHAR(6), GETDATE(), 112)))


2. Create a function which will return passed year and month in numbers e.g.

CREATE FUNCTION dbo.GetYearwithMonth(year INT, month INT)
RETURNS INT
AS

DECLARE sYear VARCHAR(4)
DECLARE sMonth VARCHAR(2)

SET sYear = CONVERT(VARCHAR(4), year)
SET sMonth = CONVERT(VARCHAR(2), month)

SET sYear = RIGHT(('0000' + sYear), 4)
SET sMonth = RIGHT(('00' + sMonth), 2)

RETURN (CONVERT(INT, (sYear + sMonth)))

3. Change my SQL Query where clause as per following syntax:

dbo.GetYearwithMonth(year, month) <= dbo.GetCurrentYearMonth()

This will reduce overhead on my coding also we will now have standard function for doing this type work in the future.

Hope this will help you in solving your problem.