May 11, 2006 #1 kss1133 Programmer Mar 22, 2006 22 US 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,
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,
May 11, 2006 #2 SQLDenis Programmer Oct 1, 2005 5,575 US 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/ Upvote 0 Downvote
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/
May 11, 2006 #3 vongrunt Programmer Mar 8, 2004 4,863 HR Less than now +5 or -5 minutes? For -5: Code: 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] Upvote 0 Downvote
Less than now +5 or -5 minutes? For -5: Code: 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]
May 11, 2006 #4 SQLBill MIS May 29, 2001 7,777 US 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 Upvote 0 Downvote
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
May 11, 2006 Thread starter #5 kss1133 Programmer Mar 22, 2006 22 US I have a table in that table is a field activedts when activedts gets to be 5 minutes old I want to delete it. Upvote 0 Downvote
I have a table in that table is a field activedts when activedts gets to be 5 minutes old I want to delete it.
May 11, 2006 #6 SQLDenis Programmer Oct 1, 2005 5,575 US 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/ Upvote 0 Downvote
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/
May 11, 2006 Thread starter #7 kss1133 Programmer Mar 22, 2006 22 US 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' Upvote 0 Downvote
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'
May 11, 2006 Thread starter #8 kss1133 Programmer Mar 22, 2006 22 US I think I found the issue. delete tblunique where datediff(mm,activedts ,getdate()) > 5 The mm needs to be changed to mi. Thanks everyone. Upvote 0 Downvote
I think I found the issue. delete tblunique where datediff(mm,activedts ,getdate()) > 5 The mm needs to be changed to mi. Thanks everyone.
May 11, 2006 #9 SQLDenis Programmer Oct 1, 2005 5,575 US 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/ Upvote 0 Downvote
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/
May 11, 2006 #10 SQLDenis Programmer Oct 1, 2005 5,575 US 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/ Upvote 0 Downvote
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/
May 12, 2006 Thread starter #11 kss1133 Programmer Mar 22, 2006 22 US I got it working I had to use mi in my datediff not mm. It is working fine now. Thanks. Upvote 0 Downvote