[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 831
  • Last Modified:

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


0
infrasafe
Asked:
infrasafe
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
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

0
 
infrasafeAuthor Commented:
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.
0
 
BrandonGalderisiCommented:
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)
0
 
infrasafeAuthor Commented:
I see that it is correct and I am not disputing that.  I am unsure why hibernate is rejecting it as a valid datetime.  I have have a similar query that is working and the date is formatted differently.  I changed my query to format the date like that and now it works.  I am not sure what changed because that date format worked previously.  Fortunately, that format will work for me.  I am not sure why it doesn't like minutes but hours will work so I guess my problem is fixed.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH a");

Select id, last_name, first_name from info where in_time >= '2008-03-12 00 AM' and in_time <= '2008-12-18 00 AM'
0
 
BrandonGalderisiCommented:
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.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now