asked on

Hibernate Query Error - Date Formatting

This is my query:

Select id, last_name, first_name from info where in_time >= '2008-03-12 00:00 AM' and in_time <='2008-12-18 00:00 AM'

It is simple and it worked before. We have several people working on this project and I cannot determine what caused it to stop working. I can run this fine in query analyzer, yet in my application, I get the following error.

2008-11-03 17:10:45,906 [JDBCExceptionReporter] ERROR - Syntax error converting datetime from character string.
2008-11-03 17:10:45,921 [InfoHandler] ERROR - could not execute query
2008-11-03 17:10:45,921 [InfoHandler] ERROR - Initiating rollback
2008-11-03 17:10:45,921 [InfoHandler] ERROR - could not execute query
2008-11-03 17:10:45,921 [InfoHandler] ERROR - org.hibernate.exception.DataException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:75)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:2150)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
      at org.hibernate.loader.Loader.list(Loader.java:2021)
      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:109)
      at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1547)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:165)
      at com.info.dataObjects.persistence.InfoHandler.SearchInfo(InfoHandler.java:321)
      at com.info.actions.InfoAction.Info(InfoAction.java:175)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
      at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
      at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
      at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
      at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
      at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
      at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
      at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
      at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
      at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
      at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
      at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:793)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:702)
      at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:571)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:644)
      at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Syntax error converting datetime from character string.
      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
      at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778)
      at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214)
      at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:763)
      at net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(JtdsResultSet.java:147)
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:346)
      at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:665)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
      at org.hibernate.loader.Loader.doQuery(Loader.java:662)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
      at org.hibernate.loader.Loader.doList(Loader.java:2147)
      ... 43 more

BrandonGalderisi

Your datetime format may be interpreting incorrectly. Try explicitly providing the date format.

Select id, last_name, first_name from info 
where in_time >= convert(datetime, '2008-03-12 00:00 AM', 20)
and in_time <= convert(datetime, '2008-12-18 00:00 AM', 20)

Open in new window

infrasafe

ASKER

I tried that before, thinking that was the problem, but it did not work. However, I did use a different datetime style code than what you used. I cut and pasted what you provided and I received the same error. I cannot make any sense out of it.

BrandonGalderisi

Then the query you are executing is not passing the dates in the same format that you are posting. I would suggest running SQL profiler to capture the ACTUAL call.

Because if you run this query, you should see that the conversion IS correct.
select convert(datetime, '2008-03-12 00:00 AM', 20), convert(datetime, '2008-12-18 00:00 AM', 20)

ASKER CERTIFIED SOLUTION

infrasafe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

I still think you MAY run into problems because '2008-12-18 00 AM' is not a recognized SQL Server DATE format. If you are passing a string containing a date into SQL server, you should always specify the format.