Not able to extract data for the datatype Timestamp

[Former Member]

Dear Friends

I am not able to extract data from the table on the field with data type timestamp. Following is the scenario in which i am working ,

I have a session bean where i am trying to retrieve all the records for a particular date .the field on which I am firing the query is "lastmodifiedat" and I am using the following query to extract data

Select e from HsSiteHeader e where (e.lastmodifiedat >= ?1 and e.lastmodifiedat <= ?2) and e.hsspcode = '8013'

here HsSiteHeader is the name of entity class and in the session bean i am setting the query parameters through this piece of code

Calendar requiredDate = Calendar.getInstance();

requiredDate.set(Calendar.DATE,requiredDate.get(Calendar.DATE)-4);

Date theDate = new Date(requiredDate.getTimeInMillis());

Timestamp dateForTestTimestamp = new Timestamp(theDate.getTime());

Calendar requiredDate2 = Calendar.getInstance();

requiredDate.set(Calendar.DATE,requiredDate.get(Calendar.DATE)-3);

Date theDate2 = new Date(requiredDate.getTimeInMillis());

Timestamp dateForTestTimestamp 2= new Timestamp(theDate2.getTime());

Query query = em.createQuery("Select e from HsSiteHeader e where (e.lastmodifiedat >= ?1 and e.lastmodifiedat <= ?2) and e.hsspcode = '8013'");

query.setParameter(1,dateForTestTimestamp);

query.setParameter(2,dateForTestTimestamp2);

but this logic is nt working out

and I am not able to retrieve records from the database though there are records that match the criteria

Does any one has an idea how to operate with timestamp datatype

[adrian_goerler]

Hi,

please double-check that the values of the timestamp fields dateForTestTimestamp and dateForTestTimestamp2 are as expected.

A bit supicous is that the field requiredDate2 is not used.

Also, please make sure that the attribute HsSiteHeader.lastmodifiedat is mapped correctly (either typed with java.sql.Timestamp or with TemporalType TIMESTAMP.

IHTH,

Adrian

[Former Member]

Hi

I checked in my entity class the data type of the field is timestamp only but it is not mapped by @TemporalType notation , what I did to solve the problem is to use StringDateFormat class and format the current date in dd-mmm-yy hh.mm.ss.SSS format , then convert the same to timestamp and then after i did the comparison

This way it worked out , but may be mapping thing is the only reason . Will try that out also