Query that automatically deletes records that are older than 365 d

Hi all,
I'm trying to build a self running query that deletes all records that are
older than 365 days. the code i've used is -
DELETE RECadvertId
RECadRecruiterId
RECadreference
RECadjobtype
RECadLocation
RECadJobTitle
RECadSalary
RECadcategory
RECadduration
RECadstartdate
RECadDatePosted
RECadCompanyID
RECadDescription
RECadDescriptionshort
RECaddayspostedfor
RECadBenefits
FROM [dbo].[RECadvert]
WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
It isn't working, any ideas?
thanksLeave out the column names:
DELETE FROM [dbo].[RECadvert]
WHERE RECadDatePosted < DATEADD(dd,-365,getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:84131850-B81C-4878-84CD-BC8C0116AD56@.microsoft.com...
Hi all,
I'm trying to build a self running query that deletes all records that are
older than 365 days. the code i've used is -
DELETE RECadvertId
RECadRecruiterId
RECadreference
RECadjobtype
RECadLocation
RECadJobTitle
RECadSalary
RECadcategory
RECadduration
RECadstartdate
RECadDatePosted
RECadCompanyID
RECadDescription
RECadDescriptionshort
RECaddayspostedfor
RECadBenefits
FROM [dbo].[RECadvert]
WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
It isn't working, any ideas?
thanks|||On Sun, 29 Jan 2006 12:55:28 -0800, GTN170777 wrote:

>Hi all,
>I'm trying to build a self running query that deletes all records that are
>older than 365 days. the code i've used is -
>DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
>It isn't working, any ideas?
>thanks
Hi GTN170777,
You've got the syntax of the DELETE statement wrong (you don't include a
column list - a DELETE will always rempve the entire row). And the WHERE
clause is incorrect too - this checks if RECadDatePosted is 365 days
older than itself (which it of course never is). You should compare
RECadDatePosted with the date that was 365 days before "now".
Something like this:
DELETE FROM dbo.RECadvert
WHERE RECadDatePosted < DATEADD(d, -365, CURRENT_TIMESTAMP)
(Note: this query is untested. Please test it on a test database first.
Also, enclose it in a transaction and check results before issuing a
COMMIT or a ROLLBACK command).
Hugo Kornelis, SQL Server MVP|||The WHERE clause is the problem. You cannot find one row where the value in
a column is not the same
as the value in that same column, i.e. the same value (take a long look at y
our WHERE clause and you
will understand). You probably want something like:
WHERE RECadDatePosted < DATEADD(d,-365,CURRENT_TIMESTAMP)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:84131850-B81C-4878-84CD-BC8C0116AD56@.microsoft.com...
> Hi all,
> I'm trying to build a self running query that deletes all records that are
> older than 365 days. the code i've used is -
> DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
> It isn't working, any ideas?
> thanks|||DELETE FROM [dbo].[RECadvert]
WHERE DATEDIFF(dd, RECadDatePosted, GETDATE()) > 365
You need the GETDATE() function to return the current date and time. The
DATEDIFF function, as used here, calculates the number of days that have
passed between a value for RECadDatePosted and today. If that number is more
than 365, that row will be deleted.
You don't need to specify column names when deleting.
"GTN170777" wrote:

> Hi all,
> I'm trying to build a self running query that deletes all records that are
> older than 365 days. the code i've used is -
> DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
> It isn't working, any ideas?
> thanks|||>> t isn't working, any ideas? <<
You might try writing SQL instead of whatever this language was. The
unit of work in SQL is a row; but you do not know that row is nothing
whatsoever like a record!! Please read a book or your error messages
before you post again.|||What hospitality. This will learn him to be curious about a subject. I
will bet he ran out and bought an entire catalog of books :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138582546.129921.115400@.g49g2000cwa.googlegroups.com...
> You might try writing SQL instead of whatever this language was. The
> unit of work in SQL is a row; but you do not know that row is nothing
> whatsoever like a record!! Please read a book or your error messages
> before you post again.
>|||Why is it that 5 other people knew what he was talking about and gave the
correct solution and you didn't?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138582546.129921.115400@.g49g2000cwa.googlegroups.com...
> You might try writing SQL instead of whatever this language was. The
> unit of work in SQL is a row; but you do not know that row is nothing
> whatsoever like a record!! Please read a book or your error messages
> before you post again.
>