delete 6 month old data form a table in mssql

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.
eawebAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
HoggZillaCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
Kevin CrossChief Technology OfficerCommented:
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
 
HoggZillaCommented:
Oh, sry. Definately < NOT > on the date. You want:
WHERE instime < GETDATE() - 180
0
 
eawebAuthor Commented:
my date format is like "11/03/2008". is this of influence on any of the give query statements and or functions????
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
eawebAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
eawebAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
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.