Yes, I didn't figure DayName() would do much good without some re-coding. Good that you found DayofMonth(). Didn't know about that one.
--Matt Robertson-- MSB Designs, Inc. http://mysecretbase.com -----Original Message----- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 9:02 PM To: CF-Talk Subject: RE: How can I modify this query so mySQL will be happy? Well that explains why the exact same query, minus the Day line, works fine. No Day() function... Only problem with the DayName function (assuming it returns the Day of the Week name and not a number) is that this function needs to return the day number in the month... 1 or 2... not Monday, etc. I found answer (after reading the docs!). It was DayofMonth. That function returns a number of the month, which is what I needed. Concerning your soapbox: I agree. The more versatile code is, the better. I'm sure there are cases when code that is completely optimized for a platform is required, but I'd rather take a small hit in performance to avoid having to rewrite a bunch of code. My current situation illustrates that. I'm having to tweak code on all 197 pages of this business app to get everything to work. :op Thanks, Matt, and Ben for your help. Rick -----Original Message----- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 11:36 PM To: CF-Talk Subject: RE: How can I modify this query so mySQL will be happy? http://www.mysql.com/doc/en/Date_and_time_functions.html They have a nice new web site, I see. I like the new format. mySQL has no DAY() function. You can try using DayName() instead. This assumes AccountCreationDate is a valid mySQL date field. And if you go there it also makes your code platform-specific. Again. <soapbox> This illustrates, IMHO, why you stay away from using anything that could be platform-specific in your SQL (and most especially in your field specs). I've always treated the db as a file cabinet only. Get files from it. Put files back; add new ones. Nothing fancy. Leave that to whatever it is you use to massage the data. </soapbox> You will hear vehement and learned arguments to the contrary that state you should write code, SQL and db-level field constraints that are optimal to the platform you are currently using, and rewrite it to a new optimum if you change platforms. I'll say no more than that in the hopes of avoiding a jihad :). --Matt Robertson-- MSB Designs, Inc. http://mysecretbase.com -----Original Message----- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 7:41 PM To: CF-Talk Subject: How can I modify this query so mySQL will be happy? This worked with MS Access, but I'm getting an error with this now... <CFQUERY Name="GetClients" Datasource="#DSN#"> Select ClientID from clients C where Day(AccountCreationDate) = #Form.Day# and Month(AccountCreationDate) = #Form.Month# and Year(AccountCreationDate) = #Form.Year# </CFQUERY> Here's the error: [TCX][MyODBC]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(AccountCreationDate) = 18 and Month(AccountCreationDa The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (34:218) to (34:263). Thanks! Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
