Hi Tim,
I would like to second what Wouter and Tom said. I think you should use
a query parameter for the date value. You can then use the setParameter
as Tom described to bind the actual date instance to the date query
parameter. The Java Persistence query language does not support date
literals. Comparing a date field with a string as in "... e.startDate >
'Sun Jan 28 19:00:00 EST 2007'" is not standardized.
Regards Michael
> When you use this strategy for temporal types, it may also be useful
> to use the Query.setParameter() methods that take a TemporalType as an
> argument:
>
> public Query setParameter(String name, Date value, TemporalType
> temporalType);
> public Query setParameter(String name, Calendar value, TemporalType
> temporalType);
> public Query setParameter(int position, Date value, TemporalType
> temporalType);
> public Query setParameter(int position, Calendar value, TemporalType
> temporalType);
>
> That way you can use the temporalType argument to specify which type
> you would like to use.
>
> -Tom
>
> Wouter van Reeven wrote:
>
>> Hi,
>>
>> On Mon, Jan 29, 2007 at 06:20:53PM -0500, Romanowski, Tim wrote:
>>
>>
>>> Right now, I am simply appending startDate.toString() to my query
>>> string,
>>> which I understand is bound to fail without some parsing or
>>> formatting, but
>>> I'm wondering what the appropriate way to do this is. Here is a sample
>>> query:
>>>
>>> SELECT e FROM MyEntity e WHERE e.startDate > 'Sun Jan 28 19:00:00
>>> EST 2007'
>>>
>>
>> Have you tried this: let's say you have a variable called
>> oldestValidDate which
>> is a Date object and has a value that represents 'Sun Jan 28 19:00:00
>> EST 2007'.
>> Create a NamedQuery with the query
>>
>> @NamedQuery(name="SelectNewerThanDate" query="SELECT e FROM MyEntity
>> e WHERE e.startDate > :checkDate");
>>
>> and call the query with the parameter oldestValidate. No formattng, no
>> converting to String or whatever, just the date object:
>>
>> @PersistenceContext EntityManager em;
>> Query query = em.createNamedQuery("SelectNewerThanDate");
>> query.setParameter("checkDate", oldestValidDate);
>>
>> By the way, EQL knows of three date functions:
>>
>> CURRENT_DATE
>> CURRENT_TIME
>> CURRENT_TIMESTAMP
>>
>> The datetime functions return the value of current date, time, and
>> timestamp on
>> the database server.
>>
>> So this should also work
>>
>> @NamedQuery(name="SelectNewerThanTwoDaysAgo" query="SELECT e FROM
>> MyEntity e WHERE e.startDate > CURRENT_DATE-2");
>>
>>
>> HTH, Wouter van Reeven
>>
>>
>>>
>>>
>>> The Entity code looks like this:
>>>
>>>
>>>
>>> @Column(name = "STARTDATE")
>>>
>>> @Temporal(TemporalType.TIMESTAMP)
>>>
>>> private Date startDate;
>>>
>>>
>>>
>>> @Column(name = "STOPDATE")
>>>
>>> @Temporal(TemporalType.TIMESTAMP)
>>>
>>> private Date stopDate;
>>>
>>>
>>>
>>>
>>>
>>> Is there an easy way to store precise timestamps into the database
>>> using
>>> Glassfish' EJBQL dialect? An easy way to construct queries using
>>> them? If
>>> not easy, I'll settle for _any_ way =) I'm also interested in how to
>>> handle timezones (such as with oracle's TIMESTAMP WITH TIMEZONE column
>>> type), but won't be greedy.
>>>
>>>
>>>
>>>
>>> Tim
>>>
>>>
>>
>>
>>
>>