Skip to main content
D
DDhillon
Known Participant
April 19, 2006
Question

Outputting data using date field

  • April 19, 2006
  • 1 reply
  • 643 views
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?
This topic has been closed for replies.

1 reply

P
paross1
Participating Frequently
April 19, 2006
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
D
DDhillonAuthor
Known Participant
April 19, 2006
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
P
paross1
Participating Frequently
April 19, 2006
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)#

Keep in mind that if your updateddate value contains a time component, then you will probably never get an exact match between the two "date" values without removing the time part.

Something like this may work better for you.

select *
from apna
where assignedto='#form.assignedto#'
and FORMAT(updateddate, "mm-dd-yyyy")=#'form.thedate'#

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices