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


infrasafeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.