Skip to main content
A
Anonymous
January 3, 2009
Question

Date filter

  • January 3, 2009
  • 1 reply
  • 548 views
I am using this code in my where statement to filter dates between a "DateFrom" and "DateThru" session varriable. When I dump the dates of both the session varriables and the Form dates, they are correct and would seem to query the correct records. Here is the code within the where statement.

<CFIF SESSION.EstimateLog.UserFilter IS NOT "">
AND (f.LastName LIKE '%#SESSION.EstimateLog.UserFilter#%' OR
e.Jobsite LIKE '%#SESSION.EstimateLog.UserFilter#%')
</CFIF>
<CFIF IsDate(SESSION.EstimateLog.DateFrom)>
AND e.BidDate >= #CreateODBCDate(SESSION.EstimateLog.DateFrom)#
</CFIF>
<CFIF IsDate(SESSION.EstimateLog.DateThru)>
AND e.BidDate <= #CreateODBCDate(SESSION.EstimateLog.DateThru)#
</CFIF>

If I have a BidDate is 12-8-08 and I enter in the form DateFrom=12-7-08 and DateThru=12-17-08 the query works. If I change the DateThru=1-8-09, the query doesn't display any records. The same thing happens if the DateFrom=11-30-08 and DateThru=12-9-08. What may be happening here. CFDUMP shows the correct dates.
This topic has been closed for replies.

1 reply

-
-__cfSearching__-
Inspiring
January 4, 2009
> I have a BidDate is 12-8-09

I would not expect the query to return any results since the ranges you mentioned are all prior to 12-08-2009. Also, are your session dates really in m-d-yy format? That is a very ambiguous format and could be problematic as the values could be interpreted multiple ways.

Start by working backwards. Run some simple checks to determine if the problem is with the data itself or the ColdFusion code:

1) Check the database table. Is the data type date/time, does it actually contain the correct dates? If not, that is your problem.

2) Run the query with _no_ criteria at all and dump the results. Do they include the BidDate you are trying to check? If not, your JOIN may be the problem.

3) Temporarily comment out all criteria in the WHERE clause _except_ the dates so they do not interfere with your results. Turn on debugging and view the actual sql ColdFusion is generating.

- Is the conversion actually producing the dates you expect?

- Also check the comparison logic (ie date >= .. <=), is it correct for your data values and the results you are trying to achieve.

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