asked on

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.

ASKER CERTIFIED SOLUTION

alorentz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Devil666

-- 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)

Devil666

actually it was supposed to be "> 30" for older than 30 days

sorry

alorentz

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.

kwitcom

ASKER

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
%>

SOLUTION

alorentz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

alorentz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwitcom

ASKER

Thnx.... Worked....