I'm writing an interface into a phpBB database where I need to perform calculations on a date. Unfortunately, phpBB stores a 'birthdate' field as varchar(10). This has uncovered a couple of challenges for me.
I want to be able to pull users by a range of dates as well as just the month. This doesn't seem very eloquent... but I thought it would get me what I needed... <cfquery name="qry" datasource="foo" > SELECT u.username, str_to_date(replace(replace(trim(u.user_birthday),"- ","-"),"- ","-"), '%e-%c-%Y') as birthday, u.user_id, u.user_inactive_reason, u.user_birthday, mid(str_to_date(replace(replace(trim(u.user_birthday),"- ","-"),"- ","-"), '%e-%c-%Y'),6,2) as birth_month FROM phpbb_users u WHERE user_birthday NOT LIKE '%- 0-%' AND user_birthday NOT LIKE '0-%' AND user_birthday NOT LIKE '0- 0-%' AND user_birthday NOT LIKE '' AND `user_inactive_reason` = 0 ORDER BY u.username </cfquery> And then I run a QofQ based upon some supplied arguments... <cfquery name="qofq" dbtype="query"> SELECT * FROM qry <cfif ARGUMENTS.fallsInMonths> WHERE birth_month BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#left(ARGUMENTS.startDate,2)#" /> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#left(ARGUMENTS.endDate,2)#" /> <cfelse> WHERE birthday BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.startDate#" /> <!--- error points to this line ---> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.endDate#" /> </cfif> ORDER BY birthday </cfquery> The end result is (line is denoted above)... Query Of Queries runtime error. Comparison exception while executing BETWEEN. Unsupported Type Comparison Exception: The BETWEEN operator does not support comparison between the following types: Left hand side expression type = "LONG". Right hand side expression type = "STRING". It seems it may have been simpler just to treat the birthday field as a string, rather than convert it into a date. Any help is very much appreciated! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312184 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4