cfquery for date not working

[Leonard B]

Greetings - I am sure what I am missing is something simple. I am trying to
query for a specific date in a database. However, when I search for a specific
date, all I get is a blank page, no error message at all. If i simply output
the entire db records, they all display just fine. Perhaps someone can shed
some light on what really should be a simple straight forward process. DB:
Access 2003 CF: CF5 & CFMX DB Field: StartDate w/ Data Type as Date/Time
(no additional attributes) DB Field: RecordTitle w/ Data Type as Text ====
Query ==== <cfquery name='GetDate' datasource='site_testing_db01'> SELECT
RecordID, StartDate, RecordTitle FROM DateSearch WHERE StartDate = 4/1/2005
<Displays blank page> </cfquery> ==== Output ==== <table width='70%'
border='1' cellpadding='0' cellspacing='0'> <tr> <td
align='center'>Date</td> <td align='center'>Record Title</td> </tr>
<cfoutput query='GetDate'> <tr> <td width='20%'
align='center'>#DateFormat(StartDate, 'mm/dd/yy')#</td> <td
width='80%'>&amp;nbsp;&amp;nbsp;&amp;nbsp;#RecordT itle#</td> </tr> </cfoutput>
</table> Thanks in advance for assistance Leonard B

[Kronin555]

The problem is that the field you're checking, StartDate, is a Date/Time and
you're just passing it a Date. What you need to do is pull just the date out of
the date/time object in the database.

WHERE to_char(StartDate,'MM/DD/YYYY') = '05/15/2005'

[The ScareCrow]

Not quite true,

The where clause is actually saying

where startdate = 4 divided by 1 divided by 2005

Plus there is no need to convert the date on the db side, just pass it an odbc
date data type

SELECT RecordID, StartDate, RecordTitle
FROM DateSearch
WHERE StartDate = #CreateODBCDate("4/1/2005")#

Ken

[Kronin555]

Ken,

You're right on the 4/1/2005, I missed that.

However, your code will do exactly what I said in my original post, which is
compare a field in the database which contains a date and time with a date-only
field (which assumes a time of midnight).

So, if StartDate is 4/1/2005 12:15:03 and you do a search with a date of
4/1/2005, it'll never match because you're comparing 4/1/2005 00:00:00 with
4/1/2005 12:15:03. It is for this reason that I had the
to_char(StartDate,'MM/DD/YYYY') in the query, to pull just the date part out
and ignore the time.

[The ScareCrow]

Kronin555

I totally agree that if the field contains time values, then my code would not
return a match. But if only the date was entered into the field then it will
always have a time value of "00:00:00". Which I would recommend unless a time
value is absolutly required. If the field does have a time value then other
methods need to be used.

As for your solution, I would be very careful with this method, as the the
date passed hs to be in the exact format as the to_char format. But again I
would not use the to_char function. I would use the DATEVALUE(date_text)
function.

But as I was taught that dates should always stay as dates, I never convert
them when quering only for display. It just creates extra processing and
possible bugs.

Ken

[OldCFer]

There's a couple of reasons not to use DB date to string conversions. First, the
DB has to convert every date in each row to a string to compare it to the
value
passed. Its much more efficient if it can compare the date value passed, to
the
stored dates directly. The other reason is that using DB functions makes the
code less portable since the functions are usually specific to that DB.

[Leonard B]

Greetings - Found a working solution to situation in the Macromedia ColdFusion
5 - Web Application Construction Kit Book - Ben Forta and Nate Weiss. WHERE
StartDate >= #CreateODBCDate(form.DateSearch)# AND StartDate <
#CreateODBCDate(DateAdd('d',form.DateSearch, 1))# Thanks for the input, took
note of what you guys provided, and added to my resource records. Leonard