Delete Dates > 6 months

Hello,

I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.

I am unable to find the statement that will execute this operation.

Anyone has an idea please.

Thanks

Markdelete from Log where Recorded_date < dateadd(month, -6, getdate())

Assuming you did record the datetime in your table.|||You mean like:

DELETE FROM yourTable
WHERE DATEDIFF(d,DateCole,GetDate()) > 180|||delete from Log where Recorded_date < dateadd(month, -6, getdate())

Assuming you did record the datetime in your table.

Be careful about months...anywhere in a month counts...

I like days better...

No?|||Be careful about months...anywhere in a month counts...

I like days better...

No?

But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.

When you execute these two statements:
SELECT DATEADD(m, -6, GETDATE())
SELECT DATEADD(d, -180, GETDATE())

You get two very different results:
2003-11-06 13:24:14.700
2003-11-08 13:24:14.700|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())

Putting the column in the function call would invalidate any index on record_date.|||Datediff returns integer values, but the DateAdd function is acceptable. I'd use:

where record_date < dateadd (month, -6, getdate())

...since the poster did specify months.|||I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.

I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.

Thanks

Mark|||Both are equally "safe". It's just a matter of whether you specs call for X months or X days.|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())

Putting the column in the function call would invalidate any index on record_date.

I'd still go with Days...

But the above is THE most important point...

My Original would cause a stage 2 predicat (nonsargable).

Very Bad

Would cause a scan...

What he hell wa I thinking (And there you go...not thinking...AGAIN...I guess the need to focus never arose *)

* JBuffet

Thanks for pointing that out|||I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.

By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.|||In Fact I used months... It works fine and suits my needs.

DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6

Thanks a lot to all of you who posted.

Mark|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())

Putting the column in the function call would invalidate any index on record_date.

Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?

I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?

*LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)|||In Fact I used months... It works fine and suits my needs.

DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6

Thanks a lot to all of you who posted.

Mark

Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:

DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())|||"dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.

"DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.|||and the reason a function call must be evaluated for each value, is because how do you know what month a date of 28488 is?

dates are stored as integers

an index on a date column is an index of integers, and if you can't decide whether an index value is one you want without doing a function on it, the optimizer says "sheesh, if i gotta evaluate all index values, i might as well just scan the table"

dateadd (dd,-180,getdate()), as blindman pointed out, gets evaluated once, prior to query execution, and its value, which happens to be 37933, can then be used in an efficient index search|||Outstanding guys...well done, and in "wow, I haven't done SQL work for 5 years or so, but I THINK I remember something about that..." language!

But now I need to go back and look at all the query work in my project *LOL*sql