?
Solved

SQL Syntax for VB Script page

Posted on 2013-06-07
5
Medium Priority
?
368 Views
Last Modified: 2013-06-07
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
Comment
Question by:romsom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39229279
DELETE FROM TimeTable
WHERE SubmissionDate <= DATEADD(m, -3, GETDATE())
0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 600 total points
ID: 39229285
For SQL Server, the syntax is:

DELETE FROM TimeTable WHERE SubmittionDate <= DATEADD(m, -3, GETDATE())
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 600 total points
ID: 39229298
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
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 800 total points
ID: 39229323
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
 

Author Closing Comment

by:romsom
ID: 39229344
Thank you very much all of you.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question