SQL - compare two fields with date

[Grantspad69]

Hi all,

I have one date field in the format "dd/mm/yyyy" that is actually a URL parameter value and a "fldAppointmentDate" field in a table of an Access 2000 database also in the "dd/mm/yyyy".

I am using the following ASP code and SQL query but it is not working

<%
Dim rsAppointments__datevalue
rsAppointments__datevalue = 01/01/2005
If (Request.QueryString("dateid") <> "") Then 
rsAppointments__datevalue = Request.QueryString("dateid")
End If
%>


- - - - - - - - - SQL QUERY - - - - - - - - -

SELECT * FROM tblAppointments WHERE fldAppointmentDate = + Replace( rsAppointments__datevalue, "'", "''") + " ORDER BY fldAppointmentStartTime ASC"

Is there a way in the SQL to compare these?

I am not sure, but I think it maybe because the URL parameter value is a string and the field in the database is a DATETIME. I have played about with the CDate() and FormatDateTime() functions but had no luck.

I only want to return rows in the database, which have dates that match with the URL parameter value.

Any suggestions would be greatly appreciated

[danfran]

rsAppointments__datevalue = 01/01/2005

Unless it's a number on the number scale, it's gotta be in double-quotes. -But don't quote me on that, hehehe...

Seriously.. change it to "01/01/2005"

With Access you can say
field > 01/01/01 or field > #01/01/01#

Only SQL Server can you say field > '01/01/01'


-Dan

[r937]

were the database fields populated with Now() by any chance?

because if they were, they will have non-zero time components

otherwise, i can't help you (i don't know whatever language that is you're using)

oh, and remember, access wants to see its date strings in hash marks

i.e. after your language has substituted its string values from the url parameter, access wants to see this --

... where fldAppointmentDate = #2/8/2005#

or if the database contains time portions,

... where fldAppointmentDate >= #2/8/2005#
and fldAppointmentDate < #2/9/2005#