You are correct. Check out CF's DaysInMonth function. Let CF do it so your Reactor code remains database agnostic. http://livedocs.macromedia.com/coldfusion/7/htmldocs/00000447.htm Yes, the site is usually agonizingly slow. Patience... :)
________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Derek P. Sent: Thursday, November 16, 2006 9:34 AM To: [email protected] Subject: RE: [Reactor for CF] Problem with isLike and DateTime fields (mysql5) Doesn’t think defeat the purpose of using reactor if your injecting proprietary functions into abstracted code? - D ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Beth Bowden Sent: Thursday, November 16, 2006 4:23 AM To: [email protected] Subject: Re: [Reactor for CF] Problem with isLike and DateTime fields (mysql5) If you are using Oracle, there's the last_day function. If you aren't and your database allows you to subtract a day from a date, subtract one day from the first of the following month. last_day(to_date('2003/03/15', 'yyyy/mm/dd')) Also, many databases will let you extract the month from a date which is cleaner than using LIKE imho. Beth On 11/15/06, Derek P. < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > wrote: Dunno, I could filter out every event for a month by just doing LIKE 2006-11-% and that's every event in November? Just how I'm used to it…I'm going to give between a shot now…just seems to make my code a bit overly complex now, because I have to determine the last day of a month based on the month I'm in. - D ________________________________ From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] On Behalf Of Chris Phillips Sent: Wednesday, November 15, 2006 12:54 PM To: [email protected] Subject: Re: [Reactor for CF] Problem with isLike and DateTime fields (mysql5) Derek, This may be a crazy question. But, why would you use LIKE for date fields? That seems really odd to me. Why aren't you using the reactor equivalent of "WHERE (dt >= '2006-01-01' AND dt < '2007-01-01' )" I'm just curious, as I have never used LIKE on a datefield and never even considered it. Regards, Chris Phillips www.dealerpeak.com Senior Application Developer On 11/15/06, Patrick McElhaney <[EMAIL PROTECTED]> wrote: I think the maxlength should just be removed on LIKE queries. IMHO, it doesn't make sense to limit the length of the value in a LIKE clause. And it keeps causing problems in Reactor resulting in ugly workarounds. ( http://trac.reactorframework.org/reactor/search?q=maxlength&wiki=on&changeset=on&ticket=on <http://trac.reactorframework.org/reactor/search?q=maxlength&wiki=on&changeset=on&ticket=on> ) Let's pretend dt is a character field and it really is only two characters wide. What would be the harm in trying this? > SELECT dt FROM table > > WHERE dt LIKE "2006-%" > The query will return no results. What's wrong with that? No results is what I expect if I search for a substring that's longer than the string itself could be. For a more realistic example, imagine you have a search form that allows people to search username or email address. The query might look like this. SELECT * FROM people WHERE username like '%arguments.searchstring%' or email like '%arguments.searchstring%' Now let's say username has a maxlength of 16 characters and email has maxlength of 255 characters. If this query was built with Reactor, I wouldn't be able to search for ' [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> '. Patrick On 11/15/06, Derek P. <[EMAIL PROTECTED]> wrote: > > > > > Hey, > > > > I need to be able to filter datetime fields with a SQL statement like this: > > > > SELECT dt FROM table > > WHERE dt LIKE "2006-%" > > > > Or something of that nature. I used the where.isLike() statement and loaded > the value 2006-11 (because is like will wrap %'s around them) and I get this > error: > > > > Message: The cause of this output exception was that: > coldfusion.tagext.sql.QueryParamTag$InvalidDataException: > Invalid data %2006-11% value exceeds MAXLENGTH setting 2.. > > > > If you check the code in the query it creates it sets the queryparam to > maxlength 2. is this a bug? Or what am I doing wrong? > > > > Thanks! > > - Derek > > > > > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > -- -- > Reactor for ColdFusion Mailing List > [email protected] > Archives at: > http://www.mail-archive.com/reactor%40doughughes.net/ > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > -- -- -- Patrick McElhaney 704.560.9117 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ <http://www.mail-archive.com/reactor%40doughughes.net/> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.430 / Virus Database: 268.14.6/535 - Release Date: 11/15/2006 3:47 PM -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.430 / Virus Database: 268.14.6/535 - Release Date: 11/15/2006 3:47 PM -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
