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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Steve HoggITCommented:
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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Steve HoggITCommented:
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
Kevin CrossChief 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.