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
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:ssWayne

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')