Not a Valid Month in Hibernate

0
votes

Hi im trying to get number of rows from table using Hibernate based on start and end Date but im Getting not a Valid Month error 

Session session = sessionFactory.getCurrentSession();         
startDate = "13-02-02 00:00:00";
endDate = "17-02-02 00:00:00";
try{
    String hql = "select Count(*) from mytable where PERIOD_START_DATETIME between '" 
                 + startDate + "' AND '" + endDate + "'";
    Query query = session.createQuery(hql);
    long count=(long) query.uniqueResult();

    return count;
} finally{
     session.close();
}

This is my table description

Name                    NULL     TYPE
NAME                    NOT NULL VARCHAR2(255 CHAR)  
PERIOD_END_DATETIME     NOT NULL TIMESTAMP(6)        
PERIOD_START_DATETIME   NOT NULL TIMESTAMP(6)        
PROD_OFFER_TERM_TYPE_ID NOT NULL NUMBER(19)
4
javaoraclehibernate
Check the date format used. If it's the american M-D-Y format then 13 and 17 are not valid months. - Thomas
Instead of concatenating the variables into the query, use bind variables and pass in the actual Java dates, not their string representations. - Mick Mnemonic
@Thomas it is YY-MM-DD mm:hh:ss - Wayne

4 Answers

2
votes

As the start/end times are SQL TIMESTAMPs in the DB, you can pass in a Timestamp object into the query as follows:

Session session = sessionFactory.getCurrentSession();

final DateFormat df = new SimpleDateFormat("yy-MM-dd");

// omitting the time part will set the time to midnight (00:00:00)
Timestamp start = new Timestamp(df.parse("13-02-02").getTime());
Timestamp end = new Timestamp(df.parse("17-02-02").getTime());

try {
    String hql = 
        "select Count(*) from mytable where PERIOD_START_DATETIME between ? AND ?";
    Query query = session.createQuery(hql)
            .setTimestamp(0, start)
            .setTimestamp(1, end);

    long count = (long) query.uniqueResult();

    return count;
} finally {
    session.close();
}
3
votes

Using string concatenation for generating SQL queries is usually a bad idea because

  • it's bad for performance (causes re-parsing of the SQL statement for every execution)
  • it's prone to SQL injection attacks

HQL supports bind variables / prepared statements, so this should work:

String hql = "select Count(*) from mytable where PERIOD_START_DATETIME between :startdate AND :enddate ";
Query query = session.createQuery(hql);
query.setParameter("startdate", startDate);
query.setParameter("enddate", endDate);

(where startDate and endDate are actual java.sql.Timestamp values, not strings).

1
votes

Make sure you actually pass date values in your query. You can use the to_date function where you specify the format of the date, as it is represented in the string.

select Count(*) 
from mytable 
where PERIOD_START_DATETIME between to_date(startDate,'DD-MM-YYYY HH24:MI:SS') AND to_date(endDate,'DD-MM-YYYY HH24:MI:SS');
0
votes
select Count(*) 
from mytable 
where PERIOD_START_DATETIME between TO_TIMESTAMP(:startDate, 'YY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP(:endDate, 'YY-MM-DD HH24:MI:SS')