[Webinar] Streamline your web hosting managementRegister Today

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

SQL Syntax for VB Script page

I would like to keep records in my database for only 3 months. When users submit a new record, I'd also like records older than 3 months get deleted. This is my delete SQL with the hard coded date. What is the correct syntax if I want to replace the hard coded date, i.e. deduct 90 days from the current date?

DELETE FROM TimeTable where SubmissionDate <='2013-03-07'

Thank you for your help
0
romsom
Asked:
romsom
3 Solutions
 
dsackerContract ERP Admin/ConsultantCommented:
DELETE FROM TimeTable
WHERE SubmissionDate <= DATEADD(m, -3, GETDATE())
0
 
Shaun KlineLead Software EngineerCommented:
For SQL Server, the syntax is:

DELETE FROM TimeTable WHERE SubmittionDate <= DATEADD(m, -3, GETDATE())
0
 
dsackerContract ERP Admin/ConsultantCommented:
Do you need to archive that data before you delete it? If so, you can INSERT INTO YourArchiveTable using the same WHERE criteria, beforehand.
0
 
Lee SavidgeCommented:
DELETE FROM TimeTable WHERE SubmissionDate < getdate() - 90

Do beware though that this will take into account the time element. To get around that:

DELETE FROM TimeTable WHERE SubmissionDate < cast(convert(nvarchar(10), getdate(), 103) as datetime) - 90
0
 
romsomAuthor Commented:
Thank you very much all of you.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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