Hibernate HQL: Convert Datetime to Date?

Question

On a SELECT query i want to convert my DATETIME column to DATE format (taking only year, month and day).

I was using DATE construct: DATE(table.column) and it was great but it seems that this construct doesen't works on SQLSERVER databases (i was working on MySql previously).

This is the error i met:

 data type for node: org.hibernate.hql.ast.tree.MethodNode +-[METHOD_CALL] MethodNode: '(' | +-[METHOD_NAME] IdentNode: 'DATE'

Someone can help me fix this? I tried using CONVERT and CAST but i met same error.

This is the query:

SELECT DATE(al.loginTime) as loginTime, (COUNT(al.hawkUser)) as numeroAccessi, al.hawkUser as hawkUser, al.logoutTime as logoutTime " +
                "FROM AccessLog as al GROUP BY al.hawkUser, DATE(al.loginTime) ORDER BY DATE(al.loginTime) DESC, al.hawkUser DESC

It seems that you are using native sql instead of HQL. what is the format of column in database.is it datetime?Please add some more details. — coder25, May 24 '17 at 09:32
Try the solution using between in HQL: https://stackoverflow.com/a/980406/1934211 — OutOfMind, May 24 '17 at 09:54
I see it as native query and not as HQL.have you tried executing query in database u can replace variable with any values.If that works ,query is correct ,but this is native query not HQL.hibernate gives you provision to run native query also. — coder25, May 24 '17 at 10:12

score 0 · Answer 1 · answered May 24 '17 at 12:44

Ok i modified several times the query, now is like this:

 String query="SELECT "
        + "  al.loginTime as loginTime,"
        + " (COUNT(hu.id)) as numeroAccessi, "
        + " hu.id as hawkUser "
                + "FROM AccessLog as al "
                + " INNER JOIN al.hawkUser hu "
                + " GROUP BY "
                + " al.loginTime,"
                + " hu.id "
                + " ORDER BY "
                + " al.loginTime DESC"
                + " hu.email  ";

loginTime is DATETIME with hours, minuted and seconds while i need to regroup them only using year month and day (it's a login list). So i still have to convert DATETIME to DATE. I can't use CAST and CONVERT since this query is for MySql and SQLServer database and the syntax is different for both.

Hibernate HQL: Convert Datetime to Date?

1 Answers1