CFquery can only find one row of table

[dfwalton]

This is weird and driving me nuts. I have a table that has 3 rows.

Bay 2010-04-01 00:00:00.000	2011-03-31 00:00:00.000	35.7500	
Bay 2011-04-01 00:00:00.000	2012-03-31 00:00:00.000	36.7500	
Bay 2012-04-01 00:00:00.000	2013-03-31 00:00:00.000	37.7500

The last col is a rate applicable during the periods defined by the 2 date fields.
Query is:

<cfquery datasource="#ds#" name = "qryCrewFee">
select  * 
from crewFee
where '#Form.EventDate#' between
	Crewfee.startDate and CrewFee.endDate 
</cfquery>

For a date between 4/1/2010 and 3/31/2011, it works like a charm. For any date on or after 4/1/11 it returns no rows.

I had the same experience earlier this week getting a MS SQL server Stored Proc to return the same data propery.

Any ideas??

Thanks

 

[r937]

perhaps '#Form.EventDate#' isn't in the correct mysql date format

... to say nothing of being wide open to sql injection

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

oh, wait, i just realized that i assumed it was a mysql database

still, perhaps it isn't in the right date format anyway

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dfwalton]

Well #form.eventDate# works for a date in the 2010-2011 year so I don't think that's the issue. Its as though the two new rows in the table are somehow not readable.

 

[LyndonOHRC]

you could try:

<cfquery datasource="#ds#" name = "qryCrewFee">
	select * 
	from crewFee
	where Crewfee.startDate>=<cfqueryparam cfsqltype="CF_SQL_DATE" value="#Form.EventDate#"> 
	And CrewFee.endDate<= <cfqueryparam cfsqltype="CF_SQL_DATE" value="#Form.EventDate#">
</cfquery>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfSearching]

where '#Form.EventDate#'

Whenever you pass in a date string instead of date object the results will always vary. You are basically leaving it up to the database to convert the string however it sees fit. So while the string is implicitly converted to some date, it may not be the correct one.

What is the value of #form.eventDate# and what is the generated sql from your cfquery?

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

http://cfsearching.blogspot.com/