?
Solved

delete 6 month old data form a table in mssql

Posted on 2008-11-03
12
Medium Priority
?
2,142 Views
Last Modified: 2013-11-30
hi,
i am in need of a query statement to run as a DTS to delete 6 month old data from a table (data) in mssql server?
i have a column (instime) with the insert date in it and it is define as datetime.
0
Comment
Question by:eaweb
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22870211
This will go to the first day of month 6 months ago and delete records prior to that.
DELETE FROM dbo.Data
WHERE instime < DateAdd(mm, -6, DateAdd(dd, -day(getdate())+1, DateDiff(dd, 0, getdate())))

Open in new window

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22870214
In a new DTS Package, drag an Execute SQL Task onto the work area.
Drag an OLEDB connection onto the work area and configure to your MSSQL.
In the Execute SQL Task, write a SQL statement something to this:
DELETE FROM MyTable
WHERE instime > GETDATE() - 180
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22870241
To delete from the date 6 months ago exactly (i.e. < 2008-05-03), then just do this:
DELETE FROM dbo.Data
WHERE instime < SELECT DateAdd(mm, -6, DateAdd(dd, 0, DateDiff(dd, 0, getdate())))

To include that day, just do this (i.e. < 2008-05-04):
DELETE FROM dbo.Data
WHERE instime < SELECT DateAdd(mm, -6, DateAdd(dd, 1, DateDiff(dd, 0, getdate())))
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.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22870252
Oh, sry. Definately < NOT > on the date. You want:
WHERE instime < GETDATE() - 180
0
 

Author Comment

by:eaweb
ID: 22870394
my date format is like "11/03/2008". is this of influence on any of the give query statements and or functions????
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22870469
You should be fine, but you can change the DELETE in any of the queries to SELECT * and see if the results look like what you want to delete.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22873018
>>my date format is like "11/03/2008". is this of influence on any of the give query statements and or functions????<<
Contrary to popular belief, SQL Server does not store datetime (or smalldatetime) with any regional formatting.
0
 

Author Comment

by:eaweb
ID: 22976763
mwvisa1,

i am try:
select * FROM errr WHERE errr_d < SELECT DateAdd(mm, -6, DateAdd(dd, 1, DateDiff(dd, 0, getdate())))

but get an error near the keyword select message.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22977723
Sorry, an extra SELECT ended up in there some how, try this:
select * 
FROM errr 
WHERE errr_d < DateAdd(mm, -6, DateAdd(dd, 1, DateDiff(dd, 0, getdate())))

Open in new window

0
 

Author Comment

by:eaweb
ID: 22978161
ok now,
if i have 2 year old data in my DB and i want to leave 1 and a half year old data in the DB from the current date on. how must the query look?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22978191
Then you would just change the query to look 18 months or 1.5 years back instead of 6 months.

SELECT *
FROM errr
WHERE errr_d < DateAdd(mm, -18, DateAdd(dd, 1, DateDiff(dd, 0, getdate())))
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22978232
Run this piece (which is what I do and why I had an extra select in my posted code :) and you will see what date you are selecting before):

SELECT DateAdd(mm, -18, DateAdd(dd, 1, DateDiff(dd, 0, getdate())))
2007-05-18 00:00:00.000 -- this is what you would get today

Therefore, all data from 5/17/2007 and older would be deleted once you change the SQL statement back to a DELETE.  To select the last year and a half, just change < to >=.

You can change the -18 up or down and see the resulting date using the previously mentioned select statement before changing your DELETE/SELECT statement to the errr table.

Best regards,
Kevin

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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