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]> 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] *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
)
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]'.
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/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
