Using SQL date() function in HQL Select

[Asad Imtiaz]

Hello guys

I am running into a problem using Hibernate, basically I need to translate the follow SQL (to HQL)

  SELECT count(*), status, date(eventDate) from call_detail where clientid = 32 and date(eventDate) GROUP BY status, date(eventDate)  

to HQL (which i am doing as follows)

 SELECT count(*), status, date(eventDate) 
from CallDetail 
where clientId = 32 and date(eventDate) 
GROUP BY status, date(eventDate)   

Now this gives me the following exception

 Exception in thread "main" java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'date' {originalText=date}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[DOT] DotNode: 'calldetail0_.CALLDATE' {propertyName=callDate,dereferenceType=4,propertyPath=callDate,path={synthetic-alias}.callDate,tableAlias=calldetail0_,className=net.voicelog.callRecording.entities.CallDetail,classAlias=null}
          +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
          \-[IDENT] IdentNode: 'callDate' {originalText=callDate}

	at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
	at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
	at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
	at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
	at net.voicelog.callRecording.ClientDriver.hqlGroupByAudioStatusDate(ClientDriver.java:34)
	at net.voicelog.callRecording.ClientDriver.main(ClientDriver.java:22)
   

but if I run the same HQL without the sql date() function in the select clause the HQL gives no exception but thats not the result I want.


Now the reason I am using the date() function is that my date column is database table is of type "timestamp" which means the format is like "dd/mm/yyyy hh:mm:ss".



Now what I have to do in my query is to group by the date part i.e. (dd/mm/yyyy) and the status.

Any idea how can i achieve this using HQL ?




What I am using is

-Hibernate 3.2.2ga
-EnterpriseDB
-JDK 1.5




Thank you.

[Asad Imtiaz]

Got it working. Used

to_char(eventDate, 'yyyy-mm-dd')

instead of date().


Problem solved