[jboss-user] [EJB 3.0] - Re: EJB-QL Convert timestamp to date

[kpiis]

"mikezzz" wrote : Hi,
  | 
  | I have a enitity with a timestamp field, but would like to query it with a date value.  Is there a portable way to truncate a timestamp to a date within an ejb-ql query?
  | 
  | E.g. something that would be functionally equivalent to the following Postgres query.
  | 
  | SELECT * FROM foo WHERE date_trunc('day', foo.timestamp) = date '2007-03-01'
  | 
  | Regards,
  | Michael Barker.


The protable way is using Query.setParameter methods:
The quote from Monson-Haefel book:

If you need to pass java.util.Date or java.util.Calendar parameters into a query, you need to use special setParameter methods:

package javax.persistence;

public enum TemporalType {
     DATE, //java.sql.Date
     TIME, //java.sql.Time
     TIMESTAMP //java.sql.Timestamp
}

public interface Query 
 {
   Query setParameter(String name, java.util.Date value, TemporalType temporalType);
   Query setParameter(String name, Calendar value, TemporalType temporalType);

   Query setParameter(int position, Date value, TemporalType temporalType);
   Query setParameter(int position, Calendar value, TemporalType temporalType);
}

A Date or Calendar object can represent a real date, a time of day, or a numeric timestamp. Because these object types can represent different things at the same time, you need to tell your Query object how it should use these parameters. The javax.persistence.TemporalType passed in as a parameter to the setParameter( ) method tells the Query interface what database type to use when converting the java.util.Date or java.util.Calendar parameter to a native SQL type.


It's working :) - I am using it.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4049611#4049611

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4049611