delete rows older than 365 days (one year)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete rows older than 365 days (one year)

Author

Topic

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2011-01-26 : 10:34:09

I need to write a script to delete any rows where the rec_date is older than 365 days

Can anyone help?

Here is my table structure:

CREATE TABLE [dbo].[forms_holder](
	[object_id] [int] IDENTITY(1,1) NOT NULL,
	[form_family] [char](1) NOT NULL,
	[file_name] [varchar](20) NOT NULL,
	[language] [char](2) NOT NULL,
	[unit] [varchar](50) NOT NULL,
	[state] [varchar](50) NOT NULL,
	[rec_date] [datetime] NOT NULL,
	[generated_file] [varbinary](max) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[object_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-26 : 10:44:57

Try this:
delete forma_holder where datediff(day,rec_date,getdate()) > 365

No, you're never too old to Yak'n'Roll if you're too young to die.

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2011-01-26 : 12:10:58

Thank you!!

related (I think) question. I questioned the developer as to why he wanted the rows deleted and he said that his queries against this table were taking too long..

I did a simple select * from ... against this table before and after my delete and it took 10 mins to return a couple hundred rows before my delete and more than 3 mins to return 37 rows after my delete..

not sure where to start looking as to why it is taking so long to query against this one table.. ????

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-26 : 12:46:50

Unbelievable!
A very simple SELECT * FROM ...
without a WHERE condition needs 3 mins to return 37 rows?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 12:48:18

[code]delete forma_holder where rec_date < dateadd(day,datediff(day,0,getdate())-365,0)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources