Go Premium for a chance to win a PS4. Enter to Win

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

Delete SQL records older than today

I have a SQL table where I need to delete records. I need to delete all the records accept todays records. The table have around 800,000 records. The table has the following fields.
ID, Curr, Bid, LastUpdate, Offer.

The LastUpdate field is the one with the dates which is in this format 28 Oct 2003 10:52

How will I go about to delete the records older than today.
0
SilverMecer
Asked:
SilverMecer
  • 4
1 Solution
 
Dishan FernandoSoftware Engineer / DBACommented:
try this...

DELETE
FROM <TableName>
WHERE LastUpdate < GETDATE()

OR

WHERE LastUpdate < '28-10-2003'
0
 
LowfatspreadCommented:
can you do the following instead

select * into #temp
where lastupdate < getdate()

truncate tablename

insert into tablename
select * from #temp


but how frequenctly do you need to do this?
you should ensure you have a full database backup before hand of the database
you'll also need to take a full database backup after the insert completes.


 
0
 
SilverMecerAuthor Commented:
I got the following error

The convertion of a char data type to a datetime data type resulted in an ou-of-range datetime value
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SilverMecerAuthor Commented:
We need to this at least once a month because the table get updated every 5 min for 24 hours a day. This a Rate server for currency convertion
0
 
SilverMecerAuthor Commented:
I have used the following string to do the deletion thank you for all your help.

DELETE FROM Rates
WHERE (LastUpdate < CONVERT(DATETIME, '2003-09-28 00:00:00', 102))
0
 
SilverMecerAuthor Commented:
Can one automate this process
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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