Forums
This topic is locked
autodelete records from table after expiration dat
Posted 02 Mar 2001 21:49:20
1
has voted
02 Mar 2001 21:49:20 Rodrigo Salinas posted:
How can I Autodelete one record from table when record is 3 months after insertion-date?Replies
Replied 03 Mar 2001 12:03:31
03 Mar 2001 12:03:31 Waldo Smeets replied:
Rodrigo, did you receive the second email I sent to you (so not the one where I asked you to post it here in the forum, but the second one)?
Waldo Smeets - www.UDzone.com Webmaster
------------------------------------------
www.UDzone.com : A Dreamweaver, Ultradev and Fireworks recourse site for developers
by developers.
------------------------------------------
Waldo Smeets - www.UDzone.com Webmaster
------------------------------------------
www.UDzone.com : A Dreamweaver, Ultradev and Fireworks recourse site for developers
by developers.
------------------------------------------
Replied 03 Mar 2001 20:31:22
03 Mar 2001 20:31:22 Bjorn Fripon replied:
Waldo,
Do you have a solution ?
I would like to receive it alswell :-)
an already thanking you,
Jezter
Do you have a solution ?
I would like to receive it alswell :-)
an already thanking you,
Jezter
Replied 04 Mar 2001 02:38:03
04 Mar 2001 02:38:03 Waldo Smeets replied:
Not yet, but we consider working on it.
Somehow Rodrigo's email keeps bouncing back to me, so I can;t get more info from him any more <img src=icon_smile_sad.gif border=0 align=middle>
Waldo Smeets - www.UDzone.com Webmaster
------------------------------------------
www.UDzone.com : A Dreamweaver, Ultradev and Fireworks recourse site for developers
by developers.
------------------------------------------
Somehow Rodrigo's email keeps bouncing back to me, so I can;t get more info from him any more <img src=icon_smile_sad.gif border=0 align=middle>
Waldo Smeets - www.UDzone.com Webmaster
------------------------------------------
www.UDzone.com : A Dreamweaver, Ultradev and Fireworks recourse site for developers
by developers.
------------------------------------------
Replied 07 Mar 2001 19:38:00
07 Mar 2001 19:38:00 George Petrov replied:
If you are using SQL Server its easy to do:
1. Make a stored procedure that deletes the old records.
Normally this will be just one DELETE query, with DATEDIFF to specify which
records
2. Schedule the execution of the SP as a JOB that runs monthly.
You are done
Greetings,
George Petrov
www.UDzone.com
1. Make a stored procedure that deletes the old records.
Normally this will be just one DELETE query, with DATEDIFF to specify which
records
2. Schedule the execution of the SP as a JOB that runs monthly.
You are done
Greetings,
George Petrov
www.UDzone.com
Replied 08 Mar 2001 09:38:43
08 Mar 2001 09:38:43 Bjorn Fripon replied:
George,
I'm no hotspot at ASP,
but what do you think of this ?
It's a "purge-page", that will
delete all records when loaded
into the browser.
<%
set cPurge = Server.CreateObject("ADODB.Command"
cPurge.ActiveConnection = "dsn=coe;"
cPurge.CommandText = "DELETE FROM event WHERE start1 < Date()-30"
cPurge.CommandType = 1
cPurge.CommandTimeout = 0
cPurge.Prepared = true
cPurge.Execute()
%>
greetz
Jezter/Bjorn
I'm no hotspot at ASP,
but what do you think of this ?
It's a "purge-page", that will
delete all records when loaded
into the browser.
<%
set cPurge = Server.CreateObject("ADODB.Command"
cPurge.ActiveConnection = "dsn=coe;"
cPurge.CommandText = "DELETE FROM event WHERE start1 < Date()-30"
cPurge.CommandType = 1
cPurge.CommandTimeout = 0
cPurge.Prepared = true
cPurge.Execute()
%>
greetz
Jezter/Bjorn
Replied 08 Mar 2001 10:17:05
08 Mar 2001 10:17:05 George Petrov replied:
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
<%
set cPurge = Server.CreateObject("ADODB.Command"
cPurge.ActiveConnection = "dsn=coe;"
cPurge.CommandText = "DELETE FROM event WHERE start1 < Date()-30"
cPurge.CommandType = 1
cPurge.CommandTimeout = 0
cPurge.Prepared = true
cPurge.Execute()
%>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
The Date() is not an SQL command,
You should use the DATEDIFF command in your SQL statement used on the Recordset query. For example:
DELETE FROM ... WHERE DATEDIFF(m,date_field,GETDATE()) <= 3
Will delete the items with date_field value within the last 3 months.
DELETE FROM ... WHERE DATEDIFF(m,date_field,GETDATE()) > 3 AND DATEDIFF(m,date_field,GETDATE()) <= 6
Will delete the records between 3 and 6 months old.
You can read more on DATEDIFF on msdn.microsoft.com/library/psdk/sql/ts_da-db_5vxi.htm
Greetings,
George Petrov
www.UDzone.com
<%
set cPurge = Server.CreateObject("ADODB.Command"
cPurge.ActiveConnection = "dsn=coe;"
cPurge.CommandText = "DELETE FROM event WHERE start1 < Date()-30"
cPurge.CommandType = 1
cPurge.CommandTimeout = 0
cPurge.Prepared = true
cPurge.Execute()
%>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
The Date() is not an SQL command,
You should use the DATEDIFF command in your SQL statement used on the Recordset query. For example:
DELETE FROM ... WHERE DATEDIFF(m,date_field,GETDATE()) <= 3
Will delete the items with date_field value within the last 3 months.
DELETE FROM ... WHERE DATEDIFF(m,date_field,GETDATE()) > 3 AND DATEDIFF(m,date_field,GETDATE()) <= 6
Will delete the records between 3 and 6 months old.
You can read more on DATEDIFF on msdn.microsoft.com/library/psdk/sql/ts_da-db_5vxi.htm
Greetings,
George Petrov
www.UDzone.com
Replied 08 Mar 2001 10:52:41
08 Mar 2001 10:52:41 Bjorn Fripon replied:
George,
I can understand the code
but will it work with a simple
Access file ?
Jezter/Bjorn
I can understand the code
but will it work with a simple
Access file ?
Jezter/Bjorn
Replied 08 Mar 2001 10:59:50
08 Mar 2001 10:59:50 George Petrov replied:
It should - I thought Access also supports the DATEDIFF command.
Otherwise you will have to do it with the more stupid:
WHERE date_field BETWEEN #01-31-00# AND #03-31-00#
because you will have to generate those days yourself.
And this will work only on Access - you can't upgrade later on to SQL Server - then you should use the DATEDIFF
Greetings,
George Petrov
www.UDzone.com
Otherwise you will have to do it with the more stupid:
WHERE date_field BETWEEN #01-31-00# AND #03-31-00#
because you will have to generate those days yourself.
And this will work only on Access - you can't upgrade later on to SQL Server - then you should use the DATEDIFF
Greetings,
George Petrov
www.UDzone.com
Replied 08 Mar 2001 11:41:10
08 Mar 2001 11:41:10 Bjorn Fripon replied:
George,
Thanks for your replies.
I'll try them both this evening.
greetz
Jezter/Bjorn
Thanks for your replies.
I'll try them both this evening.
greetz
Jezter/Bjorn
Replied 11 Mar 2001 19:17:54
11 Mar 2001 19:17:54 Bjorn Fripon replied:
George,
I tried some of you suggestions ..
Actually this is the code I used
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/deletedatum.asp" -->
<%
set ddatum = Server.CreateObject("ADODB.Command"
ddatum.ActiveConnection = MM_deletedatum_STRING
ddatum.CommandText = "DELETE FROM Autozoekertjes WHERE DATEDIFF(m,fDatum,GETDATE()) <= 3"
ddatum.CommandType = 1
ddatum.CommandTimeout = 0
ddatum.Prepared = true
ddatum.Execute()
%>
The result was this error :
Microsoft OLE DB Provider for ODBC Drivers fout '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GETDATE' in expression.
/test.asp, regel 11
Can you help me define this function ?
I would appreciate it a lot
greetz
Jezter/Bjorn
I tried some of you suggestions ..
Actually this is the code I used
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/deletedatum.asp" -->
<%
set ddatum = Server.CreateObject("ADODB.Command"
ddatum.ActiveConnection = MM_deletedatum_STRING
ddatum.CommandText = "DELETE FROM Autozoekertjes WHERE DATEDIFF(m,fDatum,GETDATE()) <= 3"
ddatum.CommandType = 1
ddatum.CommandTimeout = 0
ddatum.Prepared = true
ddatum.Execute()
%>
The result was this error :
Microsoft OLE DB Provider for ODBC Drivers fout '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GETDATE' in expression.
/test.asp, regel 11
Can you help me define this function ?
I would appreciate it a lot
greetz
Jezter/Bjorn
Replied 14 Mar 2001 23:42:17
14 Mar 2001 23:42:17 George Petrov replied:
In Access you should use DATE() instead of GETDATE()
GETDATE() is for SQL Server only.
Greetings,
George Petrov
www.UDzone.com
GETDATE() is for SQL Server only.
Greetings,
George Petrov
www.UDzone.com