deleting all records over 6 months old

[bill]

I want to delete all records that are 6 months older than when the
script is run.

Here is my attempt, it gets a syntax error.

DELETE from tasks where completed < date_sub('now()', interval 6
months)

I have tried lots of variations on the theme, still can't get it
right.

completed is a datetime column

What is the correct syntax ?

bill

[Captain Paralytic]

You didn't bother to post the error message.

And the correct syntax is as given in the manual.

[bill]

Thanks Captain

If I could have figured out the correct syntax from the manual, I
wouldn't have made myself vulnerable to comments like that.
The error message is:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'months)' at line 1
bill

[Andrew C.]

"bill" <wil...@TechServSys.com> wrote in message
news:b4f62054-cc11-400c...@r24g2000prf.googlegroups.com...

>I want to delete all records that are 6 months older than when the
> script is run.
>
> Here is my attempt, it gets a syntax error.
>
> DELETE from tasks where completed < date_sub('now()', interval 6
> months)
>
> I have tried lots of variations on the theme, still can't get it
> right.

You have a couple of minor issues. Looking at the MySQL online manual, it
appears that all of the 'unit' part values that can be passed into
DATE_SUB() (as listed in the DATE_ADD() section) are singular, e.g. 'MONTH',
'HOUR', 'QUARTER' etc. rather than 'MONTHS', 'HOURS' or 'QUARTERS'.

In addition, you have embedded the (intended) call to the NOW() function
inside a string, which means it's just a string of characters, not a
function call. To clarify, try:

SELECT 'NOW()';

then:

SELECT NOW();

So, try the following to see if the new 'completed < DATE_SUB()' expression
is choosing the kind of rows you want to delete, and the right kind of
number of them:

SELECT * FROM tasks WHERE completed < DATE_SUB(NOW(), INTERVAL 6 MONTH)
LIMIT 5;

SELECT COUNT(*) FROM tasks WHERE completed < DATE_SUB(NOW(), INTERVAL 6
MONTH);

A.

[Andrew C.]

"Andrew C." <nons...@totally.made.up> wrote in message
news:Um6Mm.116372$cj4....@newsfe14.ams2...

> SELECT * FROM tasks WHERE completed < DATE_SUB(NOW(), INTERVAL 6 MONTH)
> LIMIT 5;
>
> SELECT COUNT(*) FROM tasks WHERE completed < DATE_SUB(NOW(), INTERVAL 6
> MONTH);

Bleah. I had intended to insert my own line-breaks into the queries above
before hitting 'Send' rather than letting the newlines fall where they may.
Just to show the world who is in charge, I'll do that again... ;-)

[Brian Cryer]

"bill" <wil...@TechServSys.com> wrote in message
news:b4f62054-cc11-400c...@r24g2000prf.googlegroups.com...

See Andrew's response.

One bit of advice (which Andrew's response implies) is that its always worth
doing a simple select in the first instance before you convert it to a
delete. That way you can do a simple visual check that the records you would
be deleting are the correct ones and that you haven't got a mistake in your
select which means you are pulling back more records than you expect.

So in the first instance:

select * from tasks where completed < date_sub(now(),interval 6 month)

and if the results are what you expect then replace "select *" with delete:

delete from tasks where completed < date_sub(now(),interval 6 month)
--
Brian Cryer
www.cryer.co.uk/brian

[bill]

On Nov 16, 4:27 am, "Brian Cryer" <not.here@localhost> wrote:
> "bill" <will...@TechServSys.com> wrote in message

Thanks Guys,

I read the manual backwards:
It says Unit Value and expected expr argument so I figured that unit
value was a number and expr arg was the literal.

Oh well.

I also appreciate the other suggestions.