Delete where timefield < getdate + 5 minutes

[kss1133]

I'm haveing trouble with trying to delete records from a table where the datetime field is less that now +5 minutes.

Thanks for any help,

 

[SQLDenis]

This will delete everything older than 5 minutes

delete TableNAme
where datediff(mm,Column,getdate()) > 5

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Less than now +5 or -5 minutes?

For -5:

delete from myTable
where myColumn < getdate() - '00:05:00'

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[SQLBill]

So you want to pretty much delete everything from your database? Or is the majority of your data in the future?

If GETDATE() = '2006-05-11 05:05:00 PM', then you want to delete everything before (less than) '2006-05-11 05:10:00 PM' correct?

-SQLBill

Posting advice: FAQ481-4875

 

[kss1133]

I have a table in that table is a field activedts when activedts gets to be 5 minutes old I want to delete it.

 

[SQLDenis]

Then this should do it

delete Table
where datediff(mm,activedts ,getdate()) > 5

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[kss1133]

Yes I would think that

delete tblunique
where datediff(mm,activedts ,getdate()) > 5

would work but it does not. The date in the field is '2006-05-11 15:06:00'

 

[kss1133]

I think I found the issue.

delete tblunique
where datediff(mm,activedts ,getdate()) > 5

The mm needs to be changed to mi.

Thanks everyone.

 

[SQLDenis]

this returns 21 for me

select datediff(mi,'2006-05-11 15:06:00',getdate())

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

what timezone are you in?
also what does select getdate() return

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[kss1133]

I got it working I had to use mi in my datediff not mm.

It is working fine now. Thanks.