Date Trim Query question

[NeptuneTG]

I inherited this applicaition and users are requesting to search by date so I am looking for a way to better query my SQL database. Here is the actual query: <cfquery name="GetPartInfo" datasource="#Plocekdsn#">
Select * From Parts Where 1=1
<cfif PartNumber neq "">
AND PartNumber like '%#PartNumber#%'
</cfif>
<cfif EONumber neq "">
AND (EONumber like '%#EONumber#%'
OR ObsoleteProject like '%#EONumber#%')
</cfif>
<cfif Description neq "">
AND Description like '%#Description#%'
</cfif>
<cfif Comments neq "">
AND Comments like '%#Comments#%'
</cfif>
<cfif Status neq "">
AND Status like '%#Status#%'
</cfif>
<cfif DateAdded neq "">
AND DateAdded like '%#DateAdded#%'
</cfif>
Order by PartNumber
</cfquery>

I added the '%#DateAdded#%' part but here is my problem, in the database the dates look like this: May 28, 2002... I would like to search by month and year. And right now it is not working correctly, it takes a long time to return the values and is just not efficient, any ideas? I know I should probably use Trim in my SQL but I am not a SQL expert...

[cf_dev2]

> it takes a long time to return the values and is just not efficient

LIKE is one of the slower comparisons. Using it in a query often means the database will not be able to utilize existing indexes that might help optimize query speed. Your query is entirely composed of LIKE comparisons. Review the execution plan to see where the bottleneck is and if there is a way to improve the query time.
http://dev.mysql.com/doc/refman/5.0/en/explain.html

> I would like to search by month and year. And right now it is not working correctly

DateAdded should be stored in a date/datetime column, not varchar. Then you could properly search by date or month and year

[tjfrevert]

I agree with cf_dev2 that you should find a way to get the date field converted to a date column instead of varchar. Then you could use SQL's "between" operator.

You *might* be able to speed up what you have now by doing some thing like:

<cfif DateAdded neq "">
<cfparam name="datelist" default="">
<cfloop from="1" to="31" index="i">
<cfset datetoadd = monthasstring(month(form.dateadded)) & ' ' & i & ', ' & year(form.dateadded)>
<cfset datelist = listappend(datelist,datetoadd,'|')>
</cfloop>
<cfset datelist = listQualify(datelist,"'","|","all")>
<cfset datelist = listChangeDelims(datelist,",","|")>
AND DateAdded IN (#datelist#)
</cfif>

You should really be using the cfqueryparam tag too.

[cf_dev2]

As you said, converting the column to datetime would be the best option.

IN (...) is akin to a series of OR statements. I don't think a query with 28 to 31 OR statements would be much better than a LIKE statement.