This question was originally asked to solve a problem when I directly queried a MySQL database...however, the query is built into a web app for one of my customer that doesn't work properly.
The user is sending out birthday cards to clients and needs to know what clients have birthdates between, say Nov 27th and Dec 3rd, no matter what the year. So, I have 4 form fields... (1) Select Earliest Birth Day (2) Select Earliest Birth Month (3) Select Latest Birth Date (4) Select Latest Birth Month >From this information, I query that database using this SQL... where and Month(Client.Client_Birthdate) >= #Form.Earliest_Birthdate_Day# and Day(Client.Client_Birthdate) >= #Form.Earliest_Birthdate_Month# and Month(Client.Client_Birthdate) <= #Form.Latest_Birthdate_Date# and Day(Client.Client_Birthdate) <= #Form.Latest_Birthdate_Month# Here's the problem... If the requested earliest and latest dates fall within the same month, everything run well...but, if two months are spanned, no results are returned. Why? Rick -----Original Message----- From: Tom Chiverton [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 22, 2006 11:08 AM To: CF-Talk Subject: Re: How can I make this query run correctly? On Wednesday 22 November 2006 15:46, Rick Faircloth wrote: > from clients > where Client_Birthdate >= '2006-11-27' > and Client_Birthdate <= '2006-12-31' where client_birthdate between <cfqueryparam value="#dateOne#" ... > and <cfqueryparam value="#dateTwo#" ... > Between is a handy SQL shortcut, and the use of cfqueryparam with real CF dates leaves formatting it correctly where it belongs, well belowe the level you need to care about. -- Tom Chiverton Helping to efficiently e-enable ubiquitous deliverables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261431 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
