Search by date gives no results at all

[mokaplanjr]

I need the users of a web to be able to query the database by entering a "From" date and a "To" date in text boxes. The table has two Date/Time fields called "EventStartDate" and "EventEndDate" which are filled by the web administrator in an HTML form using the dd/mm/yyyy format.
The Access 2000 db correctly recognizes the entries as dates (tested switching from Short Date to Long Date and back in Table view).

The best I can get (meaning no error codes) is the results page showing no results.

Here is the stuff.

1. The search form:

<form action=&quot;results.cfm&quot; method=&quot;post&quot;><input type=&quot;hidden&quot; name=&quot;type&quot; value=&quot;DateSearch&quot;>
SEARCH FROM THIS DATE
<input type=&quot;text&quot; name=&quot;BeginDate&quot; size=&quot;20&quot;>
TO THIS DATE
<input type=&quot;text&quot; name=&quot;EndDate&quot; size=&quot;20&quot;>
<input type=&quot;submit&quot; value=&quot;SEARCH&quot;>
</form>

2. The results page (result.cfm&quot

<cfquery datasource=&quot;DB_DSN&quot; name=&quot;Query&quot; dbtype=&quot;ODBC&quot;>
SELECT * FROM Cal
WHERE EventStartDate <= #Form.BeginDate#
AND EventStartDate >= #Form.EndDate#
ORDER BY SecOrder ASC
</cfquery>

The idea is that if the event is in the db as starting on 3/2/2001 and ending on 3/3/2001 and I enter in the search fields (BeginDate) 3/1/2001 and (EndDate) 3/4/2001, this is should return the event since it falls inside the query dates.

What this gets me is a results page with no returns. The best I can find in going through all of the posts here is that date queries with CF is at best very difficult.

MO

 

[AMayer]

It's probably because you're comparing an alphanumeric value to a date value. I think that when you insert an alphanumeric value into the database the driver converts it to a date at that time. I don't think it does that when you're making a comparison. Someone please correct me if I'm wrong. I'm no database guru.

Try first converting the form fields to dates. For example:

<cfset Date1 = CreateDate(#Mid(FORM.BeginDate,7,4)#, #Mid(FORM.BeginDate,1,2)#, #Mid(FORM.BeginDate,4,2)#)>
<cfset Date2 = CreateDate(#Mid(FORM.EndDate,7,4)#, #Mid(FORM.EndDate,1,2)#, #Mid(FORM.EndDate,4,2)#)>

Then use the CreateODBCDate function in your query:

<cfquery datasource=&quot;DB_DSN&quot; name=&quot;Query&quot; dbtype=&quot;ODBC&quot;>
SELECT * FROM Cal
WHERE EventStartDate <= #CreateODBCDate(Variables.Date2)#
AND EventStartDate >= #CreateODBCDate(Variables.Date1)#
ORDER BY SecOrder ASC
</cfquery>

Andrew
amayer@sonic.net

 

[mokaplanjr]

Thanks for the quick response. I tried your idea out. Combining the CreateDate and Mid function successfully converted the form entry to a date, though the position/character count variables of the Mid function require the user enter the month and day as two integers (i.e. March = 03) or you get an error. No biggie... just show 'em an example.

However, the results page still returns nothing found to match the query. Booger, ain't it.

What really tweeks my schnoodle is that as expensive as ColdFusion [ver. 4.5.1] is, the documentation (and the Que book [ver. 4.0], all seem to make a point of avoiding the whole &quot;query by date&quot; thing.

How can I face my kids when they ask, &quot;Daddy, why can't I search your site by date. Billy's daddy made him a site and he can search by date. Is his daddy better than my daddy?&quot;

I plan to write directly to Allaire. For the sake of the children, I'll post the responses for all to see.

 

[Tisman]

Don't feel too bad. I'm running into almost exactly the same problem. Not to mention, I've been at this problem for 2 weeks before I discovered - tada! - the date is being converted to something completely different from what has been specified!

My code looks like the following:
<cfset form.startdate = #createodbcdate(05/27/2001)#>
<cfset form.enddate = #createodbcdate(05/31/2001)#>

<cfquery datasource=&quot;events&quot; name=&quot;results1&quot;>
select distinct(eventid) as dis_event from daily
where listdate >= #form.startdate# and listdate <= #form.enddate#
</cfquery>

To accomodate not getting errors for lack of entering the date correctly, I had broken down the form so that beginning day month and year are separate dropdowns with the proper values (ie 05/27/2001).

Today when I finally hard coded the start and end values for 05/27/2001 through 05/31/2001 on screen, it gave me the weird values: {d '1899-12-30'} through {d '1899-12-30'} through a cfoutput to show form.startdate and form.enddate.

This makes me wonder where the glitch is. Have you tried outputting the start and end values after using the createodbcdate function? I'm curious to see if it's the same problem.

Glenn

 

[strantheman]

hi mokaplanjr

Ahh yes the dreaded Access 2000 date syntax. I experiences similar problems recently when migrating from a SQL server environment to a MS Access one.

I wouldn't jump on Allaire just yet, remember its our responsibility to collect data from the user in the proper format. Id agree with Tisman, seperate drop downs are your best bet. That way you can have 6 form variables that will build 2 dates.

<!--- preparing your date --->
<CFSET temp.begindate = &quot;#form.mo1#/#form.day1#/#form.yr1#&quot;>
<CFSET temp.enddate = &quot;#form.mo0#/#form.day0#/#form.yr0#&quot;>
<!--- --->

As long as your form fields are collecting proper dates, and your #form.begindate# and #form.enddate# are in valid date formats (3/2/2001, 3-2-2001) all you need to do is wrap those variables with the createodbcdate() function.

<!--- your new query --->
<cfquery datasource=&quot;DB_DSN&quot; name=&quot;Query&quot; dbtype=&quot;ODBC&quot;>
SELECT * FROM Cal
WHERE EventStartDate <= #createodbcdate(temp.BeginDate)#
AND EventStartDate >= #createodbcdate(temp.EndDate)#
ORDER BY SecOrder ASC
</cfquery>
<!--- --->

let me know if i helped

 

[smack1]

Hey guys, I have been having troubles with this myself. I know you will get that weird 1899 date if you query a date field that was not entered in an odbc format. When I incert a date into a db field I now use the

#createodbcdate(date)#.

Also, use this when you compare dates entered via a form.

WHERE dbDate <= #createodbcdate(form.Date)#

I'm not sure how to convert an existing dbDate that isn't in an odbc format.

Someone correct me if I am wrong, I'm new at this too.
Hope this helps.