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
romsomAsked:
Who is Participating?
 
Lee SavidgeConnect With a Mentor Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
DELETE FROM TimeTable
WHERE SubmissionDate <= DATEADD(m, -3, GETDATE())
0
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
For SQL Server, the syntax is:

DELETE FROM TimeTable WHERE SubmittionDate <= DATEADD(m, -3, GETDATE())
0
 
dsackerConnect With a Mentor Contract 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
 
romsomAuthor Commented:
Thank you very much all of you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.