criteria between oracle date w/o to_date()

[geezenslaw]

Hello All, I have the dubious task of creating a grails criteria using
between on a Begin Date and an End Date on an Oracle 10g instance.

Asking this on StackOverflow doesn't work either.

The only problem is using SimpleDateFormatter Oracle mangles the date
and so no results are returned in the Grails Service I have created just
for this target effort.

*************************************************************************************************************************************************
def criteria = DomainAccount.createCriteria()

         criteria.list() {
             eq('fiscalYear', fiscalYear)
             if(someID)
                 like('someId', someID)
             between('domainDate', beginDate, endDate) // this is
getting hammered by Oracle
             if(someCode)
                 like('someCode', someCode)
             if(anotherCode)
                 like('anotherCode', anotherCode
             not {
                 eq('anotherCode', '0000')
             }
             order('someId', 'asc')
             order('someDate', 'asc')
             order('someCode', 'asc')
             order('anotherCode', 'asc')
             firstResult(offset)
             cache(true)
             maxResults(max)
         }

         println "Begin Date: " + beginDate
         println "End Date: " + endDate
*************************************************************************************************************************************************

The printlns above usually don't display anything believable.

And, the criteria returns an empty map.

Any ideas? (other than using SQL)

Please advise, David.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Wayne Fay]

>            if(someID)
>                like('someId', someID)
>            between('domainDate', beginDate, endDate) // this is getting
> hammered by Oracle
>            if(someCode)

Perhaps there is some (good?) reason the between just doesn't work
with Oracle Dates.

Did you try gt(...) and lt(...) as a replacement to the between?

Wayne

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[geezenslaw]

Hi Wayne, thnx for the reply.

Yep, compile time complaint about:

and {
     lt(endDate)
     gt(beginDate)
}

lt in grails.orm.HibernateCriteriaBuilder cannot be applied to
(java.util.Date).

I suspect I could try java.sql.Date but similar attempts to use sql Date
have failed.

Thnx, David.


On 06/12/2012 06:36 AM, Wayne Fay wrote:

>>             if(someID)
>>                 like('someId', someID)
>>             between('domainDate', beginDate, endDate) // this is getting
>> hammered by Oracle
>>             if(someCode)
> Perhaps there is some (good?) reason the between just doesn't work
> with Oracle Dates.
>
> Did you try gt(...) and lt(...) as a replacement to the between?
>
> Wayne
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>      http://xircles.codehaus.org/manage_email
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[lucastex]

Tou have to define witch field to query against:

and {
   lt('field', endDate)
   gt('field', beginDate)
}

Le mardi 12 juin 2012, David Brown a écrit :

Hi Wayne, thnx for the reply.

Yep, compile time complaint about:

and {
   lt(endDate)
   gt(beginDate)
}

lt in grails.orm.HibernateCriteriaBuilder cannot be applied to (java.util.Date).

I suspect I could try java.sql.Date but similar attempts to use sql Date have failed.

Thnx, David.


On 06/12/2012 06:36 AM, Wayne Fay wrote:

           if(someID)
               like('someId', someID)
           between('domainDate', beginDate, endDate) // this is getting
hammered by Oracle
           if(someCode)

Perhaps there is some (good?) reason the between just doesn't work
with Oracle Dates.

Did you try gt(...) and lt(...) as a replacement to the between?

Wayne

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

  http://xircles.codehaus.org/manage_email

--

Lucas Teixeira

[sergiomichels]

Hi, beginDate and endDate have the correct values? In the database they are dates with time or without?

I'm using oracle 11g and between in my criteria without any errors...

Regards,

Sérgio

On Tue, Jun 12, 2012 at 12:51 PM, Lucas F. A. Teixeira <[hidden email]> wrote:

Tou have to define witch field to query against:

and {
   lt('field', endDate)
   gt('field', beginDate)
}

Le mardi 12 juin 2012, David Brown a écrit :

Hi Wayne, thnx for the reply.

Yep, compile time complaint about:

and {
   lt(endDate)
   gt(beginDate)
}

lt in grails.orm.HibernateCriteriaBuilder cannot be applied to (java.util.Date).

I suspect I could try java.sql.Date but similar attempts to use sql Date have failed.

Thnx, David.


On 06/12/2012 06:36 AM, Wayne Fay wrote:

           if(someID)
               like('someId', someID)
           between('domainDate', beginDate, endDate) // this is getting
hammered by Oracle
           if(someCode)

Perhaps there is some (good?) reason the between just doesn't work
with Oracle Dates.

Did you try gt(...) and lt(...) as a replacement to the between?

Wayne

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

  http://xircles.codehaus.org/manage_email

--

Lucas Teixeira