Rick Faircloth wrote: > Hi, all.yes, some of us are working today. > > I'm trying to output client info for clients who > have birthdates between two designated months > and days.not years. > > For example, I need to return all clients who have > a birthday including and between Nov 27 and Dec 3, > no matter what year. > > I tried > > where Month(Client_Birth_Date) >= '#Form.Earliest_Birth_Date#' > and Day(Client_Birth_Date) >= '#Form.Earliest_Birth_Date#' > and Month (Client_Birth_Date) <= '#Form.Latest_Birth_Date#' > and Day (Client_Birth_Date) <= '#Form.Latest_Birth_Date#' > > and that code works (MySQL) when the birthdates fall in the same > month, such as Nov 5 through Nov 12, but when the birthdates > span two months, such as Nov 27 through Dec 3, the query returns > no results. >
I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) >= #Form.Earliest_Birth_Day#) OR Month(Client_Birth_Date) > #Form.Earliest_Birth_Month#) <cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month> <!---This takes care of cases where the months go over a new year---> OR <cfelse> AND </cfif> ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# AND Day(Client_Birth_Date) <= #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) < #Form.Latest_Birth_Month#) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:261618 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
