Delete Records Using A Date Field

[madhouse]

I've got a table with three fields:

ref (Autonumber)
notice (memo)
expdt (Date)

I'm trying to use an SQL statement to delete all records where expdt < the current date (Now). Here is the code I'm using:

<%
If Request.Querystring(&quot;act&quot = &quot;clean&quot; Then

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open ConnStr

strExpdt = cdate(Now())

sql = &quot;DELETE FROM tblNboard WHERE expdt < &quot; & Now()

conn.Execute sql

conn.close
set conn = nothing

strResponse = &quot;All expired notices have successfully been deleted.&quot;

End If
%>


However, this just gives me the following error:

Syntax error (missing operator) in query expression 'expdt < 11/12/2003 19:54:25'.

What am I doing wrong??

 

[mwolf00]

Just use the databases date...This is a SQL Server example.

sql = &quot;DELETE FROM tblNboard WHERE expdt < getDate()&quot;

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[Dynapen]

Actually you can do the below, where you will end up with the DB Servers date. But if you need the web servers date, then just wrap the last line in ' '.

sql = &quot;DELETE FROM tblNboard WHERE expdt < '&quot; & Now() & &quot;'&quot;

What was probably happening was that the date was coming in without anything to identify it as a date or a string. Becuase it wasn't a number the DB figured it was a field, and gave you the error.

Try adding the ' and let me know how it works.



The money's gone, the brain is shot.....but the liquor we still got.

 

[madhouse]

mwolf00 - I tried your suggestion but got the following error:

Undefined function 'getDate' in expression.


Dynapen - I tried your suggestion but got the following error:

Data type mismatch in criteria expression.

 

[madhouse]

OK, I tried the following SQL statement:

sql = &quot;DELETE * FROM tblNboard WHERE expdt < # &quot; & Now() & &quot; #&quot;

and it deleted all the records in the table up the 12/11/2003......for some reason it doesn't delete the other hundred or so records in the table up to todays date (11/12/2003)!!

So I'm getting somewhere, but any ideas why it's not deleting all the records??

P.S. Just for your info these dates are GMT and the server is also GMT.

 

[GaryC123]

What database are you using?

http://www.intouchmedialtd.com

 

[madhouse]

I'm using an Access 2000 database.

 

[GaryC123]

sql=&quot;delete * from tblNboard where expdt<'&quot; & cdate(date) & &quot;'&quot;
conn.execute(sql)

http://www.intouchmedialtd.com

 

[GaryC123]

Actually cdate won't work, you're passing the date in in uk format and Access works in american format
<%
strDate=month(date) & &quot;/&quot; & day(date) & &quot;/&quot; & year(date)
sql=&quot;delete * from tblNboard where expdt<'&quot; & strDate & &quot;'&quot;
conn.execute(sql)
%>

http://www.intouchmedialtd.com

 

[madhouse]

GaryC123 - when trying your SQL statement I first got a Data type mismatch error, so I removed the single quotes and tried it again. This time I got the same result as before where it's only deleting all the records upto the 12/11/2003!!!

 

[GaryC123]

Have you tried my second example? And confirm if you are using Uk date format

http://www.intouchmedialtd.com

 

[madhouse]

I tried your second example but that isn't deleting any records, and I can confirm that I'm using the UK date format. This is an example of the records in the table:

ref expdt notice
39 14/11/2003 18:54:22 LS01969,LS02022,& LS02018 being shipped w/end 8/11.
47 14/11/2003 14:53:47 2 mile times > Graeme 3:53, Sheraz 3:36, Paul 3:02
49 24/11/2003 15:52:58 LS01972,LS02036 Shipped 14/11 AWB No: GD706636443
50 16/11/2003 08:21:34 Welcome to live domain of leapfrog - inter:act
51 18/11/2003 10:27:41 Order No.LS01748/LS01842/LS01979 has been delivered to PTA today.
52 19/11/2003 07:52:49 Received Order No.LS01972/LS02036 through TNT, check it and delivered it to PTA same day.
53 21/11/2003 08:13:27 LS02025 - 1883 - 2009 - 2020 - received today and sent to PTA. they will get it 20th Nov.
55 23/11/2003 08:48:38 &quot;LS01969 - LS02022 - LS02018 received today will get delivered on monday
&quot;
57 16/12/2003 06:04:28 Messaging section is now having graphical FACES !
62 18/12/2003 22:47:27 Test the updated notice board.

 

[GaryC123]

How are you getting the date? Is it just 12/12/03?

http://www.intouchmedialtd.com

 

[madhouse]

When I add a record to the table I'm using:

objRecordset(&quot;expdt&quot = cdate(Now() + Request.Form(&quot;validfor&quot

&quot;validfor&quot; is a number between 1 and 14 in a dropdown list. In other words the message will be valid for between 1 and 14 days from Now().

 

[madhouse]

When I get the date (strDate) it's being returned as 12/11/2003.

 

[madhouse]

I just tried changing strDate to = day(date) & &quot;/&quot; & month(date) & &quot;/&quot; & year(date) and it's still not deleting any records!

 

[onpnt]

wouldn't it be easier to go into your database and chagne the format to Short Date and just use Date()

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[onpnt]

after setting the LCID also to your region

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[onpnt]

also, access does need encapsulation of dates by the # # in case you took those out

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[onpnt]

a bit more of a idea of what I mean
Session.LCID = 2057
response.write now() & &quot;<br>&quot;
response.write time() & &quot;<br>&quot;
response.write date() & &quot;<br>&quot;

Session.LCID = 1033
response.write now() & &quot;<br>&quot;
response.write time() & &quot;<br>&quot;
response.write date() & &quot;<br>&quot;

you can use this information to delete all dates in both formats. however I would be careful not to delete dates (if it's possible) that are formatted for the US and it in reality is Nov.'s date. you can validate for the LCID at taht point and deelte upon that setting

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]