|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can't be rocket science to delete data older than 30 days?Can it ?
is it this simple? Or is it supposed to > ? DELETE FROM LOGRECS WHERE datediff(d,getdate(), time)<=30 Try:
DELETE FROM tbl WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ; -- Anith Anith Sen wrote:
> Try: When in doubt, don't delete.> > DELETE FROM tbl > WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ; > > -- > Anith SELECT MAX(time_col) -- DELETE FROM tbl WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ; Measure twice, cut once. You're saying within the last 30 days. This statement might be more logical
to follow: DELETE LogRecs WHERE [time] < GETDATE() - 30; or DELETE LogRecs WHERE [time] < DATEADD(DAY, -30, GETDATE()); Show quote "Kevini" <Kev***@discussions.microsoft.com> wrote in message news:5882D56F-7395-46A4-A49E-D0B096A47D8F@microsoft.com... > Can it ? > > is it this simple? Or is it supposed to > ? > DELETE FROM LOGRECS > WHERE datediff(d,getdate(), time)<=30 I only want the last 30 days so nothing earlier than May 29 or should I say
records form today and back 30 days Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > You're saying within the last 30 days. This statement might be more logical > to follow: > > DELETE LogRecs > WHERE [time] < GETDATE() - 30; > > or > > DELETE LogRecs > WHERE [time] < DATEADD(DAY, -30, GETDATE()); > > > > > > "Kevini" <Kev***@discussions.microsoft.com> wrote in message > news:5882D56F-7395-46A4-A49E-D0B096A47D8F@microsoft.com... > > Can it ? > > > > is it this simple? Or is it supposed to > ? > > DELETE FROM LOGRECS > > WHERE datediff(d,getdate(), time)<=30 > > > >I only want the last 30 days But your subject says "data older than 30 days"If you want rows that are within the last 30 days, then WHERE [time] > DATEADD(DAY, -30, GETDATE()) AND [time] <= GETDATE() Ouch! I hate seeing a column containing a date named [Time]...How utterly
and needlessly confusing. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:u6h3KR7mGHA.4620@TK2MSFTNGP05.phx.gbl... > You're saying within the last 30 days. This statement might be more > logical to follow: > > DELETE LogRecs > WHERE [time] < GETDATE() - 30; > > or > > DELETE LogRecs > WHERE [time] < DATEADD(DAY, -30, GETDATE()); > > > > > > "Kevini" <Kev***@discussions.microsoft.com> wrote in message > news:5882D56F-7395-46A4-A49E-D0B096A47D8F@microsoft.com... >> Can it ? >> >> is it this simple? Or is it supposed to > ? >> DELETE FROM LOGRECS >> WHERE datediff(d,getdate(), time)<=30 > > > Ouch! I hate seeing a column containing a date named [Time]...How utterly Hey, I didn't name that column, don't shoot the messenger! :-)> and needlessly confusing. Quick, hide - I can hear Celko coming...
Show quote >>Ouch! I hate seeing a column containing a date named [Time]...How utterly >>and needlessly confusing. >> >> > >Hey, I didn't name that column, don't shoot the messenger! :-) > > > > Kevini wrote:
> Can it ? It helps to have the rocket pointed in the right direction... :-)> > is it this simple? Or is it supposed to > ? > DELETE FROM LOGRECS > WHERE datediff(d,getdate(), time)<=30 WHERE DATEDIFF(d, time, GETDATE()) > 30 Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
way:-) Show quote "Tracy McKibben" wrote: > Kevini wrote: > > Can it ? > > > > is it this simple? Or is it supposed to > ? > > DELETE FROM LOGRECS > > WHERE datediff(d,getdate(), time)<=30 > > It helps to have the rocket pointed in the right direction... :-) > > WHERE DATEDIFF(d, time, GETDATE()) > 30 > Kevini wrote:
> Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right Houston, we have lift-off!> way:-) > Kevini wrote:
> Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right So, is it really a rocket, or are you just happy to see us ;)> way:-) > /impslayer, aka Birger Johansson  |
|||||||||||||||||||||||
Other interesting topics