[thelist] CF: MySQL Matching date/time values with date type?

Frank lists at frankmarion.com
Fri Feb 6 17:11:20 CST 2004


I'm trying to retrieve records whose value is stored as a datetime with a 
value that is a date, and I keep getting zero records returned on the 
following (abbreviated) query. The value that I'm searching against is 
found  formatted as such

index.cfm?search_date=2004-01-24

SELECT
[snip]
, l.delivery_date               <- date, returns fine
, l.date_order_placed   <- date/time fails
[snip]
, c.date_ordered                <- date/time fails
FROM order_log l
LEFT JOIN order_carts c
  ON c.order_log_id = l.order_log_id
<cfif Len(search_date)>
WHERE date_order_placed = #CreateODBCDate(search_date)#
</cfif>
ORDER BY date_ordered DESC;

I want to be able to find a record created on  2004-02-05 11:35:38.0  to be 
found when I search for {d '2004-02-04'}

If I convert my url.value to a date/time it'll still fail, because the 
times won't match. Date/DateTime casting functions don't seem to be 
applicable in this version of MySQL 3.x

Can anyone offer some suggestions?

Thanks.


--
Frank Marion     lists at frankmarion.com      Keep the signal high.  



More information about the thelist mailing list