[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

SQL query to delete table data greater than one year.

Hi all!

I need to create a job where only one year of data is kept in a table for compliance.  So anything greater than a year has to be deleted.

Thanks!
kouts1
0
kouts1
Asked:
kouts1
1 Solution
 
cyberkiwiCommented:
Use this as the date filter in the job query

Delete from mytable Where datecol < dateadd(y, -1, getdate())
0
 
käµfm³d 👽Commented:

DELETE FROM table_name
WHERE DATEDIFF(year, date_column, getdate()) >= 1

Open in new window

0
 
DanMerkCommented:
Please Note: While DATEDIFF is the better option, I would read the following about it:

http://www.beansoftware.com/T-SQL-FAQ/Subtract-DateTime-SmallDateTime.aspx

The bottom line is that it has a habit of rounding up, so a DATEDIFF in hours of 61 minutes will register as 2 hours. Therefore, if you need exact precision, you might want to use

DELETE FROM table_name
WHERE DATEDIFF(day, date_column, getdate()) >= 365
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
I would stick to whole days, at least.

And you don't want to manipulate the table column.

So do something like this:

DELETE FROM tablename
WHERE dateColumn < DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

The DATEADD(DAY ... DATEDIFF(DAY ... strip the time off GETDATE(), so that you delete data for whole days, not based on the time-of-day when the query is run.
0
 
Lara FEACommented:
Just for performance I would suggest first define variable based on your definition of "1 year "
then use this variable in your delete query
You do need to have date column in your table that will define "age" of the row.

declare @dt datetime
set @dt = dateadd(year, -1,convert(varchar(10),getdate(),101))
select @dt
-- delete from myTable where dateCreated >=@dt
0
 
Scott PletcherSenior DBACommented:
SQL should be able to optimize a literal value **far better** than a variable.  So use a literal constant whenever you can.

Note that GETDATE() is considered a literal constrant, since SQL replaces it at the start of the batch, prior to creating the query plan.
0
 
cyberkiwiCommented:
While the discussion is refreshing, the first comment is already correct.

dateadd(y, -1, getdate())

will give you exactly 1 year prior to current time to the millisecond, including leap year calculation.
I don't believe stripping the time info is relevant, since this is an archival operation that is run every day so that extra op is moot.
0
 
kouts1Author Commented:
So this query should keep from current day back until one year?
dateadd(y, -1, getdate())

0
 
cyberkiwiCommented:
yes

< dateadd(y, -1, getdate())
0
 
Scott PletcherSenior DBACommented:
>> I don't believe stripping the time info is relevant, since this is an archival operation that is run every day so that extra op is moot. <<

You're lucky, since your procedures seem never to fail :-) .

I would want a re-run of a failed proc to produce *exactly* the same results as the initial run if it had to be re-run later that day for any reaon.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now