Results 1 to 3 of 3
Feb 8, 2005, 17:03 #1
SQL - compare two fields with date
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 appreciatedcheers,
Feb 8, 2005, 18:08 #2
rsAppointments__datevalue = 01/01/2005
- Join Date
- Jan 2005
- New York City
- 0 Post(s)
- 0 Thread(s)
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'
Feb 8, 2005, 18:12 #3
- Join Date
- Jul 2002
- Toronto, Canada
- 59 Post(s)
- 3 Thread(s)
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#