Outputting data using date field

[DDhillon]

I have a query that outputs the result based on date. I m writing it as:

select * from apna where assignedto='#form.assignedto#' and
updateddate='#form.thedate#'

The query looks like this with variables:

select * from apna where assignedto='Dave' and updateddate='4-19-2006'

It is showing no result. What am I doing wrong here?

[paross1]

A couple of questions.

1. What is your database type?
2. What is the data type of the updateddate column?
3. What is your data souce connection type (ODBC, JDBC, etc.)?
4. What is the version of ColdFusion?

Phil

[DDhillon]

1. What is your database type?
MS Access

2. What is the data type of the updateddate column?
Date/Time

3. What is your data souce connection type (ODBC, JDBC, etc.)?
ODBC

4. What is the version of ColdFusion?
Coldfusion MX

[paross1]

Try using createodbcdatetime() on your form.thedate variable within the query
to convert it to a date/time object.

select *
from apna
where assignedto='#form.assignedto#'
and updateddate=#createodbcdatetime(form.thedate)#

Phil

[DDhillon]

Thank you Phill but I m still getting this error:

Error Executing Database Query.
Too few parameters. Expected 1.

The Error Occurred in E:\ApnaCalls\callstrack.cfm: line 40

38 : <td>Size</td>
39 : <td>Date Called</td>
40 : </tr>
41 : <cfoutput query="gettrack">
42 : <tr>




--------------------------------------------------------------------------------

SQL select * from apna where assignedto='Sukh' and updateddate={ts
'2006-04-19 00:00:00'}
DATASOURCE apnacalls
VENDORERRORCODE -3010
SQLSTATE 07002

The way date is stored in database is like : 4/19/2006

Could that be a reason?

[paross1]

I thought that you said that it was a date/time object. What happens if you do
this?

select *
from apna
where assignedto='#form.assignedto#'
and CDate(updateddate) = #createodbcdate(form.thedate)#

Phil