Delete records based on dates

I have a database "DB" and I want to delete records from table "MyRecords" which are of future ("RecordDates" which are more than today just to avoid da开发者_运维技巧te change on system) and less than x no. of days from today.

That "x" may vary. Moreover, I want to execute same type of command with month and year also.

I am using SQL Server and C#.

To delete records which are in the future and less than n days in the future you could use T-SQL such as this:

    DELETE FROM DB.table WHERE 
    (date > GETDATE() AND date <  DATEADD(day, 5, GETDATE()));

In this case n is 5.

You can see all the arguments to DATEADD() at DATEADD (Transact-SQL)

This query will delete all records that are later than today's date, but less than 30 days in the future. You could replace "30" with a variable so you could determine how many days in the future to delete.

DELETE FROM Table 
WHERE
TABDate > GETDATE() and TABDate < DATEADD(day, 30, GETDATE())

UPDATE

To delete all records less than 30 days in the past, you would change the query to look like this:

DELETE FROM Table 
WHERE
TABDate > DATEADD(day, -30, GETDATE()) AND TABDate < GETDATE()

Also note that all these examples are calling GETDATE() which also has a time component as well as a date, so care must be taken in that anytime you see a statement like < GETDATE() you are not just deleting records with a date before, say, 2011-09-29, you are deleting all records with a date before '2011-09-29 17:30'. So be aware of that if you table dates contain times as well.

You can use the query DELETE FROM DB.table WHERE date > now() or WHERE date > unix_timestamp(), depending on how you are storing your dates. (i.e. date-time vs. timestamp).