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
