ASP, SQL and Dates

[Ricjd]

I am having a lot of trouble with these three. When I try to have a WHERE clause which include [tt]'someDate > 04/01/2003'[/tt] it brings up ALL of the records. If I have WHERE clause which includes [tt]'someDate < 04/01/2003'[/tt] it brings NONE of the records. On both of the clauses it should bring up about half of the records.

Below is the test ASP code that I am using to get this to work.
[tt]
<%
Set bookRS = Server.CreateObject(&quot;ADODB.recordset&quot
Set bookConn = Server.CreateObject(&quot;ADODB.Connection&quot
bookConn.open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & server.mappath(&quot;db\cpRental.mdb&quot & &quot;;&quot;
strSQL = &quot;SELECT bookingID FROM booking WHERE bookingStatus = 'book' AND bookingFinish < 01/05/2003&quot;
bookRS.Open strSQL, bookConn
i = 0
Do While not bookRS.EOF
i = i + 1
response.write (i & &quot;: &quot; & bookRS(&quot;bookingID&quot & &quot;<br>&quot

bookRS.MoveNext
loop
bookRS.Close
bookConn.close
set bookConn=Nothing
set bookRS=Nothing
%>
[/tt]
If anyone can help it would be much appreciated. I am using IIS for the ASP, but I am not too sure for the SQL server. It is being run on Win 2000 Server.

Thanking you in advance

Rick

 

[mwolf00]

If 'bookingFinish' is a date field, then you should enclose the dates in '#' signs (for Access databases). If the field is text, then you should consider changing it to a date field...

strSQL = &quot;SELECT bookingID FROM booking WHERE bookingStatus = 'book' AND bookingFinish < #01/05/2003#&quot;


Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[Ricjd]

thanks mwolf00.

I figured it out just after i posted, so just came back to tell ya all.

thanks very much though for your reply.

Rick

 

[newbiepg]

normally i use
mydate = cDate(01/05/2003)
strSQL = &quot;SELECT bookingID FROM booking WHERE bookingStatus = 'book' AND bookingFinish < '&quot; &mydate& &quot;' &quot;