May 27, 2009 at 12:42 pm
I'd like to prune my database every month to purge out 2 month old data. I have 2 date fields to choose from, but other than manually going in and doing it I was wondering it there's a way to automate it to where, say, on the 10th of every month it would clear out March data (in this case since it's May now).
May 27, 2009 at 1:05 pm
You can always set up a Job in SQL Server agent that runs every day that will purge data more than 60 days old or two months old based on the date field.
-Roy
May 27, 2009 at 1:14 pm
Well, I guess I was kinda asking how to set a job like that up. Oracle has a sysdate() function that you can use to do date math. Is there anything like that in SQL Server 2005?
May 27, 2009 at 1:20 pm
getdate()
May 27, 2009 at 1:22 pm
Ah...thank you so much!
May 27, 2009 at 1:23 pm
GetDate is the function to get the current date. let us say you want to find out what is was the date 10 days back, it will be getdate() - 10
If you want to find out what was the date 2 months back you could use DATEADD. You can find it in Books on line with examples.
For example
USE AdventureWorks;
GO
SELECT DATEADD(month, -2, GetDate()) AS twomonthsback
GO
-Roy
May 27, 2009 at 1:30 pm
Roy Ernest (5/27/2009)
GetDate is the function to get the current date. let us say you want to find out what is was the date 10 days back, it will be getdate() - 10If you want to find out what was the date 2 months back you could use DATEADD. You can find it in Books on line with examples.
For example
USE AdventureWorks;
GO
SELECT DATEADD(month, -2, GetDate()) AS twomonthsback
GO
That's exacltly what I want to do, or more precisely, compare the dateadd output to the date column in my tables. Kinda like;
delete from
May 27, 2009 at 1:34 pm
Glad we could help.
-Roy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply