• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Rolling 30 days (delete older)

Rolling 30 days (delete older)
I want to setup something to take todays date and then -30 and then delete the older ones.
SELECT * FROM labels WHERE (fnh_dt <= 'today - 30 days')

I think i have an idea of using Var, but need the know how to delete all the records at once, after viewing them on page, then click to delete.
0
kwitcom
Asked:
kwitcom
  • 4
  • 2
  • 2
3 Solutions
 
alorentzCommented:
Get records within past 30 days:

sql = "SELECT * FROM labels WHERE (fnh_dt >= '"&date-30&"')"

and delete older then 30 days:

sql = "DELETE FROM labels WHERE (fnh_dt < '"&date-30&"')"
0
 
Devil666Commented:
-- check the records that are older than 30 days
SELECT * FROM labels
WHERE DATEDIFF(dd, fnh_dt, GETDATE() < 30)


-- Delete the records
DELETE
FROM labels
WHERE DATEDIFF(dd, fnh_dt, GETDATE() < 30)
0
 
Devil666Commented:
actually it was supposed to be "> 30" for older than 30 days

sorry
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
alorentzCommented:
To clarify:

My answer works for both Access and SQL Server.  Devil666's answer is used for SQL Server only, using DATEDIFF and GetDate() functions.


@Devel666 - note, your answer is improperly formatted SQL...test it.
0
 
kwitcomAuthor Commented:
getting Error on the delete page:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '*'.
/Admin/Del30days.asp, line 29

Code:

<%
'CONNECTION
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=001; User ID=xxxxxx; Password=xxxxxx"
Set ObjRs = Server.CreateObject("ADODB.Recordset")

'DECLARATIONS
dim dia, strSQL, Conn
dia = Date()
strSQL = "DELETE * FROM labels WHERE (fnh_dt <= '"&date-30&"')"
Conn.Execute(strSQL)
%>
<h1 align="center">ZONE 4 <br>
<font size="3">(More than 30 Days Old)<br>
<br>
Listings have been deleted</font></h1>
<%
'CLOSE
conn.close
set conn = nothing
%>
0
 
alorentzCommented:
Whoops, take out the *:

strSQL = "DELETE FROM labels WHERE (fnh_dt <= '"&date-30&"')"
Conn.Execute(strSQL)
0
 
alorentzCommented:
Actually, no whoops...I didn't have  * in my answer <grin>.  But still get rid of it.
0
 
kwitcomAuthor Commented:
Thnx.... Worked....
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now