Hi all,

I think my brain has gone into meltdown. ASP with MS SQL database.

How do I delete all records that are older than 30 days. I know there are a few ways of doing it but I tried this:

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open Database_Path
'Create an ADO recordset object
Set rsAddEntry = Server.CreateObject("ADODB.Recordset")

'Initalise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM Candidates;"
'Set the lock type so that the record is locked by ADO when it is updated
rsAddEntry.LockType = 3 
'Query the database
rsAddEntry.Open strSQL, adoCon

'Loop through the recordset
Do While not rsAddEntry.EOF

        'If date is older than 30 days delete it
        If DateDiff("d",rsAddEntry("CreateDate"),pExpired) > 60 Then
            'Delete the selected record
            rsAddEntry.Delete
        End If

        'Move to the next record in the recordset
            rsAddEntry.MoveNext

        Loop

        'Close the recordset
        rsAddEntry.Close

Why isn't it working or is there a better way of doing it?

Thank

I'd avoid selecting at all - go straight for the delete!

  strSQL = "DELETE FROM Candidates WHERE CreateDate < (GETDATE() - 30);"
  rs.Execute(strSQL)

Not that this will delete records that are 720 hours old (30 * 24) rather than 30 days. You can do some more fancy stuff with DATEADD and DATEPART if you need to, but the above should suffice.

A word of warning though: are you SURE you want to be deleting? In many cases, you need a record of what happened, in case of problems. I'd suggest adding a BIT column to the table, flagging the record as "Inactive" - and running an UPDATE query instead to set all records older than the 60 days to 'inactive'

+1 for soft delete, but remember that all your select statements must then filter WHERE IsDeleted=0

And If I remember my ASP Classic at all, when using scud's delete statement you dont create an ADODB.Recordset, you just create an ADODB.Command, set the SQL statement a-la scud's example, and call execute.

Thank you for replying. Yes it definitely needs deleting as the data will have been copied over to another table. I don't currently have the column set as a date column just varchar as I am adding the date created in ASP. Will this only work with a date column? If so what date format will I need for MS SQL Server 2008 express?

Rule 1. Never store dates in VARCHAR fields
Rule 2: See rule 1.

Dates don't have a format until you display them, in a database they are usually stored as a number behind the scenes.

So what format would I need in order for CreateDate < (GETDATE() - 30) to work?

The reason I have it as varchar is because my table is just being used as a go between between a form and two programs called Inaport and SalesLogix so the date needs to be in the format 22/01/2012 and not the American format. Does that make sense?

Take a look at the CAST or CONVERT commands.

   strSQL = "DELETE FROM Candidates WHERE CAST(CreateDate AS DateTime) < (GETDATE() - 30);"

or

   strSQL = "DELETE FROM Candidates WHERE CONVERT(DateTime,CreateDate,103) < (GETDATE() - 30);"

Your CreateDate column will need to hold correctly formatted dates or your sql will blow out with an out of range error.

I'm a bit rusty with MS SQL but I think CAST is preferred over CONVERT

Thanks Techno. Do I not need to convert my column datatype with that then?

No - CAST/CONVERT will convert the column datatype on the fly for that query. I'm assuming this is a one off data maintenance task? If so then using CAST should be ok.

If this is going to be a scheduled job or task that is run regularly then I'd look at getting your table storing the correct data type (DateTime or Date) instead of a Varchar.

No it's going to be a regular thing that I am now doing. Only got told it on Friday!

I'll look at changing the data type then and see if there are any that match how I need to store it. Cheers, no doubt I'll be back here shortly

Originally posted by Suzy

The reason I have it as varchar is because my table is just being used as a go between between a form and two programs called Inaport and SalesLogix so the date needs to be in the format 22/01/2012 and not the American format. Does that make sense?

Dates often used to bite me on the bum when doing ASP Vbscript, and my 'mentor' taught me to use VARCHAR fields. However, this becomes a mighty pain in the arse when doing any form of date comparison.

I really, strongly recommend saving the date in a DATETIME or SMALLDATETIME column, and formatting the output for the other apps using an ASP function.

Inserting a date into the DB: get the date from the form data. At this point it will be a string, in whatever format you use. For the sake of demonstration, I assume that your form POSTS the date in the format dd/mm/yyyy

As ASP sucks somewhat in terms of functionality, you then create a SQL-friendly date format, again as a string. SQL will automatically convert this to a DATETIME

 '' Returns date in YYYYMMDD format
Function dbDate(dt) 
    dbDate = year(dt) & right("0" & month(dt), 2) &_ 
    right("0" & day(dt),2) & " " & formatdatetime(dt,4) 
End Function 

When you extract the date from the database to use later, you can either format it using the SQL function:

 SELECT convert(varchar, CreateDate , 103) AS 'CreateDate' FROM Candidates

Or handroll an ASP function to convert to UK format date.

PS - I 'think' that you could just use the built-in ASP function FormatDateTime - IF you set this at the top of your page:

 Session.LCID = 2057

Unfortunately I'm not the one pulling the data out as this will be an automated function created by a program called Inaport which is why I need to make sure everything is stored exactly as it is needed.

Could you create two columns -- a datetime field just for comparison, and a second varchar for presentation? Not the most efficient approach but sounds like it'd simplify things for what you want to achieve.

I'm so thick! Yes that sounds like the perfect solution! Think I'm still in Monday morning mode - not woke up yet

Cheers Stickman

Originally posted by Stickman

Could you create two columns -- a datetime field just for comparison, and a second varchar for presentation? Not the most efficient approach but sounds like it'd simplify things for what you want to achieve.

What happens when these two fields go out of sync?

(when, not if)

Then you're fucked, although of course one can assume that the datetime field will always be canonical so it would be a trivial operation to update the presentation varchar field from it, if you were worried that it was out of sync.

It's not a good solution and I'd avoid it if a better alternative were available. It really depends on the situation. If this is just a transitional table -- for moving data from one place to another -- I don't see the harm. If it's an important table and the possibility of inconsistency between the two fields is likely to cause real problems, then for sure you'd want a more robust approach.

That's a point how do I update and insert the current date into a column where the datatype is date? Do I have to get it formatted correctly in my code first for the database to accept it?

Yes it is just a transitional table for holding and updating the form data until it is pulled out when it is deleted by Inaport, or as I am trying to do, deleted if it is over a month old.

Doesn't help when I am informed of this till near the end of the project so I hadn't planned for it.

Well CAST definitely doesn't work so I think I might be back trying to get my original code to work by getting ASP to work out which records are over so many days old and then deleting them as I think we have considered every other option.

When inserting dates into a database, i'd suggest always going with ISO Format, so when writing your insert statement format dates as YYYYMMDD. This has no ambiguity, so you wont run into US/UK date format issues.

So you are responsible for the INSERT of data, and then Inaport does everything it needs to directly access the database?

I have a cunning plan in that case: create a View.

A View is simply explained as a virtual Table. If you have queries that join multiple tables and you use them pretty often, you could create a View that presents the same data, but apparently as a single table.

In your case, however, you would create the View with all the same columns as your Table, except that the CreateDate column would be a varchar with dd//mm/yyyy format, for Inaport to query.

This means that you can still do easy date comparisons on the main table - and the View will update immediately.

To create a View is similar to creating a table:

   CREATE VIEW CandidatesView AS
    SELECT 
         columnName,
         anotherColumnName,
         yetAnother,
         convert(varchar, CreateDate , 103) AS 'CreateDate'
    FROM Candidates

If you need the "table" that Inaport queries to be named "Candidates" simply change your table name and set the View to be called "Candidates"

Problem is Inaport will be automatically deleting the record after it has read it and copied it over so a view table isn't possible :(

Ok.

Keep your date column a Varchar.

This should do it:

 strSQL = "DELETE FROM Candidates WHERE CONVERT(DATETIME, CreateDate , 103) < (GETDATE() - 30);"
 rs.Execute(strSQL)

Your original code would be pretty slow as you are hitting the database once to select all rows, then again to delete each row that is older than the days. A single SQL statement is almost always preferable, although they sometimes get a bit complicated.

Cheers I'll give it a try

Sorry I know I'm a pain and my jobs are never straight forward along with the fact I really need to get out of my comfort zone and get away from classic ASP LOL

... also worth looking into Stored Procedures, which are significant speed improvement to inline SQL, but also are much more secure than inline SQL.

To be honest I had forgotten so much of the old-school ASP that I had to research!