Native Query date formats between different database engines

Question

I am creating a native query for Hibernate using the following code

    final Query countQuery = entityManager.createNativeQuery( countSql );

    if ( countCriteria.getStartDate() != null ) {
        final String startDate = countCriteria.getStartDate().toString();
        countQuery.setParameter( START_DATE_PARAMETER, startDate );
    }

Where startDate on my class is an org.joda.time.DateTime and toString() returns "2019-05-13".

These query parameters work fine in production, which is running against MS Sql Server. (ick!)

Now, the unit test is running against HSQLDB. I'm providing the same values and it throws:

javax.persistence.PersistenceException: org.hibernate.exception.DataException: data exception: invalid datetime format

When it attempts to run the query.

Since the parameter is formatted exactly the same, I'm assuming that the problem is the format of the date string for HSQLDB.

So, now what? How do I move forward with this? I've checked the setParameter format that takes TEMPORAL or whatever, but that won't work for JODA time.

I've already talked to my tech lead about not using native queries, but using hibernate queries, but it has fallen on deaf ears. Do I have another option so I don't have to battle my tech lead to write unit tests for our DAOs?

Possible duplicate / solution.... stackoverflow.com/questions/33700721/… — SS_DBA
– SS_DBA, Commented May 20, 2019 at 17:39
@WEI_DBA, No I found that one. That only works for Oracle. MS SQL Server doesn't implement the TO_DATE function, but uses it's custom version. So, that solution won't work for me. — Thom
– Thom, Commented May 20, 2019 at 17:51
@WEI_DBA, unless, of course, you're seeing something I don't. — Thom
– Thom, Commented May 20, 2019 at 17:52
Ok. Thanks.. I'm not familiar with hibernate. Just trying to help out. But if it's looking for time, plug in 0's for the time format to see if that gets you by. — SS_DBA
– SS_DBA, Commented May 20, 2019 at 17:55
HSQLDB 2.4.x expects a java.time object. Previous versions expect a java.sql object. A string is converted to the java object but it expects the hour:minute:second part as well as the date part. — fredt
– fredt, Commented May 20, 2019 at 18:03

Thom · Accepted Answer · 2019-05-21 11:16:31Z

0

Well, I'm embarrassed to admit how long it took me to figure this one out.

I simply converted the LocalDate to a java date, then used that to set a TemporalType parameter.

    Date date = localDate.toDate();
    countQuery.setParameter(parmName, date, TemporalType.DATE);

This seems to be working fine.

answered May 21, 2019 at 11:16

Thom

15.3k34 gold badges117 silver badges201 bronze badges

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

Collectives™ on Stack Overflow

Native Query date formats between different database engines

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related