Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

One of the Oracle tables looks like this, I dont have the option of changing this:

REPORTING_PERIOD | REPORTING_DATE (Oracle DATE type)
-------------------------------
1140               01-FEB-12
1139               01-JAN-12

The JPA entity (with Hibernate as the provider) which looks like this :

@Column(name="REPORTING_PERIOD")
private long reportingPeriod;

@Temporal( TemporalType.DATE)
@Column(name="REPORT_DATE")
private Date reportDate; //java.util.Date

Now, let us go through my unit tests: (I am using Spring Data JPA for repositories) The below line queries the DB by REPORTING_PERIOD column

ReportingPeriod period1 = reportingPeriodRepository.findByMaxReportingPeriod();
assertNotNull(period1); // works fine and entity is fetched
System.out.println(period1.getReportDate());

The out put of SOP is 2012-02-01 - Notice the automatic conversion from value in db 01-FEB-12

Now, If I query directly by date using '01-FEB-12', as I am doing below, I dont get any results:

ReportingPeriod period2 = reportingPeriodRepository.findByReportDate(period1.getReportDate());
assertNotNull(period2);

Notice that, I am using date field from the same entity which I could successfully fetch in the previous statement.

Nor this works :

DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
ReportingPeriod period3 = reportingPeriodRepository.findByReportDate(df.parse("2012-02-01"));
assertNotNull(period3);

Any help on how can I query ( with HQL will also be ok) by REPORTING_DATE as the param when the value in db is 01-FEB-12 is greatly appreciated.

share|improve this question
Are you sure the format you are using is correct to parse the string to Date? Can you try DateFormat df = new SimpleDateFormat("yyyy-MM-dd");? – Bhesh Gurung Apr 28 '12 at 18:10
@βнɛƨн Ǥʋяʋиɢ -- Nope, I had actually tried that earlier too, but does not work. Strangely, Hibernate does not report any errors, only no results. – SRK Apr 28 '12 at 18:25
Can you give us the query (NamedQuery or Critera query or whatever it is you use.) I think the error/problem is in the query or in the method findByReportDate – esej Apr 28 '12 at 18:35
1  
try a date range from >= 2012-02-01 to < 2012-02-02 in your query and see if that brings any results. sometimes with dates, you think you're working with a date and its really storing it as a date-time – Bruce Lowe Apr 28 '12 at 19:40
1  
I think like @BruceLowe, Oracle always stores dates with time (even if you don't show it). If you can query the DB with pure sql (not with hibernate) see what you get for this query: select to_char(REPORTING_DATE, 'dd/mm/yyyy hh24:mi:ss') from IR_REPORTING_PERIOD – A.B.Cade Apr 29 '12 at 5:47
show 3 more comments

1 Answer

I think there is some explicit date format conversion while obtaining the result in reportingPeriodRepository.findByMaxReportingPeriod();

Hence we can check whether we get the data using the same format as database format

Change

DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); 
ReportingPeriod period3 = reportingPeriodRepository.findByReportDate(df.parse("2012-02-01"))

to

DateFormat df = new SimpleDateFormat("dd-MMM-yy"); 
ReportingPeriod period3 = reportingPeriodRepository.findByReportDate(df.parse("01-FEB-12"))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.