Hibernate - When use sqlQuery, face "data exception: invalid datetime format" issue

Question

My code looks like:

Session session = sessionFactory.getCurrentSession();
        Query countQuery = session.
                createSQLQuery(queryString);
        Long totalFilteredRecords = ((Number)countQuery.uniqueResult())
                .longValue();

The queryString is:

SELECT COUNT(id)
FROM SOME_TABLE
WHERE
  to_char(LAST_UPDATED_DATE, 'yyyy-mm-dd hh:mi:ss')
BETWEEN
  '2015-11-13 11:40:03'
AND
  '2015-11-13 11:34:03'

LAST_UPDATED_DATE is Date type.

I run this SQL on SQLDeveloper and it works well, but failed when run via Hibernate:

org.hibernate.util.JDBCExceptionReporter: SQL Error: -3407, SQLState: 22007
org.hibernate.util.JDBCExceptionReporter: data exception: invalid datetime format: yyyy-mm-dd hh:mi:ss

My database is Oracle.

Could anybody help clarify is there anything wrong?

@Siyual No, it's correct oracle format. mm is for months, mi - for minutes. — Dmitriy
– Dmitriy, Commented Nov 13, 2015 at 19:56
Can you make sure, that your date is in correct format? Try to log values of LAST_UPDATED_DATE somewhere. For example, in log table. — Dmitriy
– Dmitriy, Commented Nov 13, 2015 at 19:58
Oh, wait! LAST_UPDATED_DATE is a column with date type? So write directly LAST_UPDATED_DATE between ... and ... — Dmitriy
– Dmitriy, Commented Nov 13, 2015 at 20:03
@Dmitry I replace date format string from lower case to upper case and it works... — coderz
– coderz, Commented Nov 16, 2015 at 21:09

void · Accepted Answer · 2015-11-13 20:00:28Z

1

it's not need to cast the date value to string and compare it, you could also use parameter for the hql query:

Query q = em.createQuery("SELECT COUNT(id) "+
     "FROM SOME_TABLE "+
     "WHERE "+
     "  LAST_UPDATED_DATE >= :startDate AND LAST_UPDATED_DATE <=:endDate";
q.setParameter("startDate",your_start_date,TemporalType.DATE);
q.setParameter("endDate",your_end_date,TemporalType.DATE);

answered Nov 13, 2015 at 20:00

void

7,9503 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coderz Over a year ago

This is good way! But due to some reason I'm not able to change the code. Thus the only way is to pass a pure SQL query...

Marmite Bomber · Accepted Answer · 2015-11-14 12:25:51Z

1

Typical constrain of a DATE column is as follows

 SELECT COUNT(id)
 FROM SOME_TABLE
 WHERE
    LAST_UPDATED_DATE  /* Column with DATE format */
 BETWEEN
   /* DATE literal using explicit formatting */
   to_date('2015-11-13 11:40:03', 'yyyy-mm-dd hh24:mi:ss')
 AND
   to_date('2015-11-13 11:34:03', 'yyyy-mm-dd hh24:mi:ss')

answered Nov 14, 2015 at 12:25

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

1 Comment

coderz Over a year ago

Yes another good way, if replace yyyy-mm-dd hh24:mi:ss to upper case, then it will work.

coderz · Accepted Answer · 2015-11-16 21:06:49Z

1

Found the answer, it is a little unexpected simple... Replace lower case date format string to upper case.

Change queryString to:

SELECT COUNT(id)
FROM SOME_TABLE
WHERE
  to_char(LAST_UPDATED_DATE, 'YYYY-MM-DD HH24:MI:SS')
BETWEEN
  '2015-11-13 11:40:03'
AND
  '2015-11-13 11:34:03'

And it works.

answered Nov 16, 2015 at 21:06

coderz

5,01912 gold badges53 silver badges80 bronze badges

Collectives™ on Stack Overflow

Hibernate - When use sqlQuery, face "data exception: invalid datetime format" issue

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related