SQL Date Interval

Question

I have a table in ms access contains startDate and EndDate, how can i delete a row from the table using SQL when the difference between the two intervals reach 6 months?i don't need to give the date by me, i need it as i'm asking... any help plz?

Answer 1

Use DateDiff:

DELETE FROM your_table
WHERE DateDiff("m", startDate, EndDate) = 6

For records 6 months and older:

DELETE FROM your_table
WHERE DateDiff("m", startDate, EndDate) <= 6

Answer 2

The general idea in MSSQL is this:

DELETE FROM Archive WHERE [Date] < DATEADD(m, -6, GETDATE())

Not sure if Access has those functions but there should be something similar.

EDIT: As I suspected, Access does suport Transact-SQL and has these 2 date functions, but they are only available for a Microsoft Access project (.adp). Hope this helps.

Answer 3

You've been given a correct answer by @OMG Ponies:

  DELETE FROM your_table
  WHERE DateDiff("m", startDate, EndDate) <= 6

...but I would tend not to use this, as it won't use indexes. Instead, I'd use this:

  DELETE FROM your_table
  WHERE StartDate <= DateAdd("m", -6, EndDate)

Because you're testing a calculation against a field and not against a literal value, any index on StartDate can be used. For large tables, this could be a significant difference.